MySQL数据类型

MySQL数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	[ - (27) , (27-1) ]	[ 0 , (28-1) ]	小整数值
SMALLINT	2 字节	[ - (215) , (215-1) ]	[ 0 , (216-1) ]	大整数值
MEDIUMINT	3 字节	[ - (217) , (217-1) ]	[ 0 , (224-1) ]	大整数值
INT或INTEGER	4 字节	[ - (231) , (231-1) ]	[ 0 , (232-1) ]	大整数值
BIGINT	8 字节	[ - (263) , (263-1) ]	[ 0 , (264-1) ]	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值

#插入数据测试下范围
mysql> create table data(tindata tinyint, smdata smallint,medata mediumint,indata int,bigdata bigint);
Query OK, 0 rows affected (0.19 sec)

mysql> insert into data values(123456789,123456789,123456789,23456789,23456789);
Query OK, 1 row affected, 3 warnings (0.03 sec)

#可以发现超出范围会以最大值进行存贮,且默认值为有符号的范围.
mysql> select * from data;	
+---------+--------+---------+-----------+-----------+
| tindata | smdata | medata  | indata    | bigdata   |
+---------+--------+---------+-----------+-----------+
|     127 |  32767 | 8388607 | 123456789 | 123456789 |
+---------+--------+---------+-----------+-----------+
1 row in set (0.00 sec)

类型修饰符

• unsigned:规定字段只能保存正的数据.

• zerofill:用0填补输出的值.默认空格填充

mysql> create table dataf (one int,two int unsigned,three int zerofill,four int unsigned zerofill);
Query OK, 0 rows affected (0.11 sec)


#显示下表的结构
mysql> desc dataf;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| one   | int(11)                   | YES  |     | NULL    |       |
| two   | int(10) unsigned          | YES  |     | NULL    |       |
| three | int(10) unsigned zerofill | YES  |     | NULL    |       |
| four  | int(10) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table dataf\G
*************************** 1. row ***************************
       Table: dataf
Create Table: CREATE TABLE `dataf` (
  `one` int(11) DEFAULT NULL,
  `two` int(10) unsigned DEFAULT NULL,
  `three` int(10) unsigned zerofill DEFAULT NULL,
  `four` int(10) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into dataf values(10,10,10,10),(-10,-10,-10,-10)
    -> ;
Query OK, 2 rows affected, 3 warnings (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 3

mysql> select * from dataf;
+------+------+------------+------------+
| one  | two  | three      | four       |
+------+------+------------+------------+
|   10 |   10 | 0000000010 | 0000000010 |
|  -10 |    0 | 0000000000 | 0000000000 |
+------+------+------------+------------+
2 rows in set (0.00 sec)

上述查询表结构中可以看到 int(11),int(10)的字样,int(M) M表示显示最大宽度.与占多少存储空间无关,只是显示的方式不同

浮点数据类型

float(N,M):表示此字段有效位数N位,小数点后面M位,超过M位,自动四舍五入

mysql> create table datat(test double(3,1));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into datat values(123.123),(1.1),(2.99);
Query OK, 3 rows affected, 1 warning (0.16 sec)
Records: 3  Duplicates: 0  Warnings: 1

#超过3位填充最大值99.9
mysql> select * from datat;
+------+
| test |
+------+
| 99.9 |
|  1.1 |
|  3.0 |
+------+
3 rows in set (0.00 sec)

字符串类型char和varchar

char后面需要一个数值修饰,来确认字符串范围.小于长度,空格自动补齐,大于长度,自动截断. char(N) N[0,255]

mysql> create table datac (test char(10));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into datac values("1234567890000"),("abc");
Query OK, 2 rows affected, 1 warning (0.20 sec)
Records: 2  Duplicates: 0  Warnings: 1

#超出的部分自动截断.
mysql> select * from datac;
+------------+
| test       |
+------------+
| 1234567890 |
| abc        |
+------------+
2 rows in set (0.00 sec)

varchar:可变长,最大支持65535,实际可用65532个字节数据.起始位和结束位会占用3字节.

时间日期类型:date time

date,time,datetime三种时间类型
mysql> create table t(d date,t time ,dt datetime);
Query OK, 0 rows affected (0.01 sec)

#now()函数插入当前日期。
mysql> insert into t values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from t
    -> ;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2019-07-27 | 11:09:33 | 2019-07-27 11:09:33 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

复合类型：enum,set

enum枚举类型，它的值需要在创建表时通过枚举方式指定

set和enum非常类似，也是一个字符串对象，可以包含0-64个成员。set类型可以从值集合中选择任意1个或多个元素进行组合

===========================enum====================
mysql> create table t (gender enum('M','F'));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t values('M'),('1'),('f'),('null');
Query OK, 4 rows affected, 1 warning (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 1

#这里可以注意到，小写会自动转换成大写，对于插入不在枚举范围之内的值，会插入第一个值'M'
mysql> select * from t
    -> ;
+--------+
| gender |
+--------+
| M      |
| M      |
| F      |
|        |
+--------+
4 rows in set (0.01 sec)


=======================set=================
mysql>  create table t1 (col set('a','b','c','d'));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t1 values('a,b'),('a,b,f'),('a,a,c');
Query OK, 3 rows affected, 1 warning (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 1

#这里注意到，对于超出范围的值，a,b,f，其中f是不允许注入到set类型中，对于重复的a,a,a，的集合将只取一次。
mysql> select * from t1;
+------+
| col  |
+------+
| a,b  |
| a,b  |
| a,c  |
+------+
3 rows in set (0.00 sec)