数据类型
整型
| 数据类型 | 含义（有符号） |
|---|---|
| tinyint(m) | 1个字节 范围(-128~127) |
| smallint(m) | 2个字节 范围(-32768~32767) |
| mediumint(m) | 3个字节 范围(-8388608~8388607) |
| int(m) | 4个字节 范围(-2147483648~2147483647) |
| bigint(m) | 8个字节 范围(+-9.22*10的18次方) |
取值范围如果加了unsigned，则最大值翻倍，例如：tinyint unsigned的取值范围为(0~256)。
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围。例如int(5)表示当数值宽带小于5位的时候在数字前面填满宽度，如果不显示指定的宽度则默认为int(11)。
举例
mysql> create table t1(id1 int,id2 int(5));
Query OK, 0 rows affected (0.08 sec)mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| id2 | int(5) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> insert into t1 values(1,2);
Query OK, 1 row affected (0.55 sec)mysql> select * from t1;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)mysql> alter table t1 modify id1 int zerofill;
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> alter table t1 modify id2 int(5) zerofill;
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> select * from t1;
+------------+-------+
| id1 | id2 |
+------------+-------+
| 0000000001 | 00002 |
+------------+-------+
1 row in set (0.00 sec)mysql> insert into t1 values(1234567,1234567);
Query OK, 1 row affected (0.51 sec)mysql> select * from t1;
+------------+---------+
| id1 | id2 |
+------------+---------+
| 0000000001 | 00002 |
| 0001234567 | 1234567 |
+------------+---------+
2 rows in set (0.00 sec)
浮点型和定点数
| 数据类型 | 含义 |
|---|---|
| float(m,d) | 单精度浮点型 8位精度(4字节) m总个数，d小数位 |
| double(m,d) | 双精度浮点型 16位精度(8字节) m总个数，d小数位 |
| decimal(m,d) | 定点数类型 10位精度 m总个数，d小数位 |
对于小数的表示MySQL分为两种方式：浮点数和定点数。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。浮点数和定点数都可以用类型名称后加“(M,D)”的方式来进行表示，“(M,D)”表示该值一共显示M位数字（整数位+小数位），其中D位位于小数点后面，M和D又称为精度和标度。例如：设一个字段定义为float(5,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。
举例
mysql> create table t2 (id1 float(5,2) default NULL,id2 double(5,2) default NULL,id3 decimal(5,2) default NULL);
Query OK, 0 rows affected (0.51 sec)mysql> desc t2;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id1 | float(5,2) | YES | | NULL | |
| id2 | double(5,2) | YES | | NULL | |
| id3 | decimal(5,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)mysql> insert into t2 values(1.23,1.23,1.23);
Query OK, 1 row affected (0.54 sec)mysql> select * from t2;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00 sec)mysql> insert into t2 values(1.234,1.234,1.23);
Query OK, 1 row affected (0.56 sec)mysql> select * from t2;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.00 sec)mysql> insert into t2 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.55 sec)mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1265 | Data truncated for column 'id3' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)mysql> select * from t2;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
3 rows in set (0.00 sec)mysql> alter table t2 modify id1 float;
Query OK, 0 rows affected (0.56 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> alter table t2 modify id2 double;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> alter table t2 modify id3 decimal;
Query OK, 3 rows affected, 3 warnings (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 3mysql> desc t2;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id1 | float | YES | | NULL | |
| id2 | double | YES | | NULL | |
| id3 | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> insert into t2 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.51 sec)mysql> select * from t2;
+-------+-------+------+
| id1 | id2 | id3 |
+-------+-------+------+
| 1.23 | 1.23 | 1 |
| 1.23 | 1.23 | 1 |
| 1.23 | 1.23 | 1 |
| 1.234 | 1.234 | 1 |
+-------+-------+------+
4 rows in set (0.00 sec)mysql> insert into t2 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.59 sec)mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1265 | Data truncated for column 'id3' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)mysql> select * from t2;
+-------+-------+------+
| id1 | id2 | id3 |
+-------+-------+------+
| 1.23 | 1.23 | 1 |
| 1.23 | 1.23 | 1 |
| 1.23 | 1.23 | 1 |
| 1.234 | 1.234 | 1 |
| 1.234 | 1.234 | 1 |
+-------+-------+------+
5 rows in set (0.00 sec)mysql> insert into t2 values(1.23456789,1.23456789,1.23456789);
Query OK, 1 row affected, 1 warning (0.23 sec)mysql> select * from t2;
+---------+------------+------+
| id1 | id2 | id3 |
+---------+------------+------+
| 1.23 | 1.23 | 1 |
| 1.23 | 1.23 | 1 |
| 1.23 | 1.23 | 1 |
| 1.234 | 1.234 | 1 |
| 1.234 | 1.234 | 1 |
| 1.23457 | 1.23456789 | 1 |
+---------+------------+------+
6 rows in set (0.00 sec)
字符串
| 数据类型 | 含义 |
|---|---|
| char(n) | 固定长度，最多255个字符 |
| varchar(n) | 固定长度，最多65535个字符 |
| tinytext | 可变长度，最多255个字符 |
| text | 可变长度，最多65535个字符 |
| mediumtext | 可变长度，最多2的24次方-1个字符 |
| longtext | 可变长度，最多2的32次方-1个字符 |
| enum(x,y,z,etc.) | 允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。 |
| set | 与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。 |
char和varchar
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
3.char类型的字符串检索速度要比varchar类型的快。
举例
mysql> create table vc(v varchar(4),c char(4));
Query OK, 0 rows affected (0.59 sec)mysql> insert into vc values('ab ','ab ');
Query OK, 1 row affected (0.51 sec)mysql> select * from vc;
+------+------+
| v | c |
+------+------+
| ab | ab |
+------+------+
1 row in set (0.00 sec)mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
| 4 | 2 |
+-----------+-----------+
1 row in set (0.01 sec)mysql> select concat(v,'+'),concat(c,'+') from vc;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab + | ab+ |
+---------------+---------------+
1 row in set (0.00 sec)
varchar和text
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值。
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。
enum
枚举类型，它的值范围需要在创建表时通过枚举方式显示指定，对1255个成员的枚举需要一个字节存储；对于25565535个成员，需要两个字节存储。最多允许有65535个成员。
举例
mysql> create table t3(gender enum('M','F'));
Query OK, 0 rows affected (0.62 sec)mysql> insert into t3 values('M'),('f'),('t'),(NULL);
ERROR 1265 (01000): Data truncated for column 'gender' at row 3
mysql> insert into t3 values('M'),('t'),('f'),(NULL);
ERROR 1265 (01000): Data truncated for column 'gender' at row 2
mysql> insert into t3 values('M'),('f'),(NULL);
Query OK, 3 rows affected (0.52 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from t3;
+--------+
| gender |
+--------+
| M |
| F |
| NULL |
+--------+
3 rows in set (0.00 sec)
从上面可以看到如果列表中不存在插入的值，则会报错。
日期时间类型
| 数据类型 | 含义 |
|---|---|
| date | 日期 ‘2008-12-2’ 4个字节 |
| time | 时间 ’12:25:36′ 3个字节 |
| datetime | 日期时间 ‘2008-12-2 22:06:44’ 8个字节 |
| timestamp | 自动存储记录修改时间 4个字节 |
举例
mysql> create table t4(d date,t time,dt datetime);
Query OK, 0 rows affected (0.03 sec)mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> insert into t4 values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t4;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2017-03-04 | 20:43:09 | 2017-03-04 20:43:09 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
数据类型的属性
| 关键字 | 含义 |
|---|---|
| NULL | 数据列可包含NULL值 |
| NOT NULL | 数据列不允许包含NULL值 |
| DEFAULT | 默认值 |
| PRIMARY KEY | 主键 |
| AUTO_INCREMENT | 自动递增，适用于整数类型 |
| UNSIGNED | 无符号 |
| CHARACTER SET name | 指定一个字符集 |
举例
mysql> create table st(Id int unsigned not null auto_increment,Gender enum('M','F') not null,Name char(15) not null,Info char(20) default 'stu',primary key (Id))auto_increment=1;
Query OK, 0 rows affected (0.02 sec)mysql> desc st;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| Id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Gender | enum('M','F') | NO | | NULL | |
| Name | char(15) | NO | | NULL | |
| Info | char(20) | YES | | stu | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)mysql> insert into st values(1,'M','xiaoming','stu');
Query OK, 1 row affected (0.00 sec)
mysql> insert into st values(2,'F','xiaohong','Teacher');
Query OK, 1 row affected (0.00 sec)mysql> select * from st;
+----+--------+----------+---------+
| Id | Gender | Name | Info |
+----+--------+----------+---------+
| 1 | M | xiaoming | stu |
| 2 | F | xiaohong | Teacher |
+----+--------+----------+---------+
2 rows in set (0.00 sec)
