MySQL数据类型总结MySQL 支持在类型名称后面的小括号内指定显示宽度，例如 int(5)表示当数值宽度小于 5

MySQL数据类型总结

数值类型

MySQL 支持在类型名称后面的小括号内指定显示宽度，例如 int(5)表示当数值宽度小于 5 位的时候在数字前面填满宽度，如果不显示指定宽度则默认为 int(11)。一般配合 zerofill 使用，在数字位数不够时用字符“0”填满。示例：

mysql> create table my_table (n1 int,n2 int(3));
Query OK, 0 rows affected, 1 warning (1.16 sec)

mysql> desc my_table;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| n1    | int  | YES  |     | NULL    |       |
| n2    | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.04 sec)

mysql> insert into my_table values(2,2);
Query OK, 1 row affected (0.42 sec)

mysql> select * from my_table;
+------+------+
| n1   | n2   |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.00 sec)

将字段类型加入zerofill：

mysql> alter table my_table modify n1 int zerofill;
Query OK, 1 row affected, 1 warning (0.89 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> alter table my_table modify n2 int(3) zerofill;
Query OK, 1 row affected, 2 warnings (0.67 sec)
Records: 1  Duplicates: 0  Warnings: 2

mysql> select * from my_table;
+------------+------+
| n1         | n2   |
+------------+------+
| 0000000002 |  002 |
+------------+------+
1 row in set (0.00 sec)

如果数值宽度大于设置，则会显示原数据，不会截断，比如n2 输入值为22222，则显示为22222，指定zerofill将会被自动认为unsigned属性。

AUTO_INCREMENT列的值一般从 1 开始，每行增加 1。在插入 NULL 到一个 AUTO_INCREMENT 列时，MySQL 插入一个比该列中当前最大值大 1 的值，可简单理解为索引列。

定义--三种方式:

CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY);

CREATE TABLE AI(ID INT AUTO_INCREMENT NOT NULL ,PRIMARY KEY(ID));

CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL ,UNIQUE(ID));

mysql> alter table my_table add column id int auto_increment not null primary key;
Query OK, 0 rows affected (0.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from my_table;
+------------+------+----+
| n1         | n2   | id |
+------------+------+----+
| 0000000002 |  002 |  1 |
+------------+------+----+
1 row in set (0.00 sec)

mysql> insert into my_table values(1,1,NULL);
Query OK, 1 row affected (0.44 sec)

mysql> select * from my_table;
+------------+------+----+
| n1         | n2   | id |
+------------+------+----+
| 0000000002 |  002 |  1 |
| 0000000001 |  001 |  2 |
+------------+------+----+
2 rows in set (0.00 sec)

小数表示

浮点数double float
定点数 decimal（内部以字符串形式存放），存储高精度数据

浮点数和定点数都可以用类型名称后加“(M,D)”的方式来进行表示，“(M,D)”表示该值一共显示 M 位数字（整数位+小数位），其中 D 位位于小数点后面，M 和 D 又称为精度和标度。超过的小数位数将会被四舍五入截断。这是非标准用法，不利于数据库迁移。

mysql> create table a_table(n1 float(3,2),n2 double(3,2),n3 decimal(3,2));
Query OK, 0 rows affected, 2 warnings (0.53 sec)

mysql> insert into a_table values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.48 sec)

mysql> select * from a_table;
+------+------+------+
| n1   | n2   | n3   |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00 sec)
//以下是默认精度值
mysql> create table a_table(n1 float,n2 double,n3 decimal);
Query OK, 0 rows affected (0.60 sec)

mysql> insert into a_table values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from a_table;
+-------+-------+------+
| n1    | n2    | n3   |
+-------+-------+------+
| 1.234 | 1.234 |    1 |
+-------+-------+------+
1 row in set (0.00 sec)

**BIT(M)可以用来存放多位二进制数，M 范围从 1～ 64，如果不写则默认为 1 位。**可以用 bin()（显示为二进制格式）或者 hex()（显示为十六进制格式）函数进行读取。

mysql> select * from a_table;
+-------+-------+------------+
| n1    | n2    | n3         |
+-------+-------+------------+
| 1.234 | 1.234 | 0x02       |
+-------+-------+------------+
1 row in set (0.00 sec)

mysql> select bin(n3) from a_table;
+---------+
| bin(n3) |
+---------+
| 10      |
+---------+
1 row in set (0.00 sec)

日期时间类型

mysql> create table t(d date,t time,dt datetime);
Query OK, 0 rows affected (2.00 sec)

mysql> desc t;
+-------+----------+------+-----+---------+-------+
| 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 t values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.41 sec)

mysql> select * from t;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2020-07-01 | 00:00:00 | 2020-07-01 00:00:00 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

在表t中插入ts timestamp 并指定默认值为CURRENT_TIMESTAMP（系统日期），新MySQL如果不指定则为null：

mysql> alter table t add ts timestamp default current_timestamp;
mysql> desc t;
+-------+-----------+------+-----+-------------------+-------------------+
| Field | Type      | Null | Key | Default           | Extra             |
+-------+-----------+------+-----+-------------------+-------------------+
| d     | date      | YES  |     | NULL              |                   |
| t     | time      | YES  |     | NULL              |                   |
| dt    | datetime  | YES  |     | NULL              |                   |
| ts    | timestamp | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------+-----------+------+-----+-------------------+-------------------+
4 rows in set (0.00 sec)

mysql> insert into t (d,t,dt) values(now(),now(),now());
Query OK, 1 row affected, 1 warning (1.77 sec)

mysql> select * from t;
+------------+----------+---------------------+---------------------+
| d          | t        | dt                  | ts                  |
+------------+----------+---------------------+---------------------+
| 2020-07-16 | 10:04:12 | 2020-07-16 10:04:12 | 2020-07-16 10:04:12 |
+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)

TIMESTAMP一个重要特点，就是和时区相关。当插入日期时，会先转换为本地时区后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。

查看当前时区：show variables like 'time_zone';

mysql> create table t(dt datetime,ts timestamp);
Query OK, 0 rows affected (1.88 sec)

mysql> insert into t values(now(),now());
Query OK, 1 row affected (0.05 sec)

mysql> select * from t;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2020-07-16 10:15:03 | 2020-07-16 10:15:03 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2020-07-16 10:15:03 | 2020-07-16 11:15:03 |
+---------------------+---------------------+
1 row in set (0.00 sec)

可以看到timestamp是与时区相关的。

TIMESTAMP的取值范围为19700101080001到2038年的某一天，因此它不适合存放比较久远的日期，下面简单测试一些这个范围：

mysql> insert into t values(now(),19700101080000);
ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 'ts' at row 1
mysql> insert into t values(now(),20380119111408);
ERROR 1292 (22007): Incorrect datetime value: '20380119111408' for column 'ts' at row 1
mysql> insert into t values(now(),'2038-01-19 11:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 'ts' at row 1
mysql> insert into t values(now(),'2038-01-19 11:14:07');
Query OK, 1 row affected (1.74 sec)

说明：TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大，因此不同版本运行结果可能不同。

日期格式：

以2020-01-01 00：00：00为例

'2020-01-01 00:00:00'
'2020-1-1 00:00:00'
'2020/1/1 00+00+00' 分隔符可以是任何符号
20200101000000
‘20200101000000’

字符串类型

CHAR 和 VARCHAR 类型

CHAR 列的长度固定为创建表时声明的长度，VARCHAR 列中的值为可变长字符串（不超过声明时长度）。在检索的时候，CHAR 列删除了尾部的空格，而 VARCHAR 则保留这些空格。

mysql> create table t(vc varchar(4),c char(4));
Query OK, 0 rows affected (0.63 sec)

mysql> insert into t values('ab  ','ab  ');
Query OK, 1 row affected (0.43 sec)

mysql> select length(vc),length(c) from t;
+------------+-----------+
| length(vc) | length(c) |
+------------+-----------+
|          4 |         2 |
+------------+-----------+
1 row in set (0.00 sec)

mysql> insert into t values('abd','abcder'); 
ERROR 1406 (22001): Data too long for column 'c' at row 1  //超长报错

BINARY 和 VARBINARY

类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。

当保存 BINARY 值时，在值的最后通过填充“0x00”（零字节）以达到指定的字段定义长度，对于一个 BINARY(3)列，当插入时'a'变为'a\0\0'。

ENUM

忽略大小写，可以用数字指代

mysql> create table t(gender enum('m','f'));
Query OK, 0 rows affected (0.61 sec)

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

mysql> select * from t;  
+--------+
| gender |
+--------+
| m      |
| f      |
| m      |
| f      |
| NULL   |
+--------+

SET

1～8 成员的集合，占 1 个字节。
9～16 成员的集合，占 2 个字节。
17～24 成员的集合，占 3 个字节。
25～32 成员的集合，占 4 个字节。
～64 成员的集合，占 8 个字节。

mysql> create table t(s set('a','b','c','d'));
Query OK, 0 rows affected (0.39 sec)

mysql> insert into t values('a,b'),('a,b,a');
Query OK, 2 rows affected (0.41 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+
| s    |
+------+
| a,b  |
| a,b  |
+------+

Set 和 ENUM 除了存储之外，最主要的区别在于 Set 类型一次可以选取多个成员,而且SET会对成员进行去重。