MySQL支持的数据类型
1、数值类型
整数类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
TINYINT | 1 | 有符号:-128 无符号:0 | 有符号:127 无符号:255 |
SMALLINT | 2 | 有符号:-32768 无符号:0 | 有符号:32767 有符号:65535 |
MEDIUMINT | 3 | 有符号:-8388608 无符号:0 | 有符号:8388607 无符号:1677215 |
INT、INTEFRE | 4 | 有符号:-2147483648 无符号:0 | 有符号:2147483647 无符号:4294967295 |
BIGINT | 8 | - | - |
浮点数类型 | 字节 | 最小值 | 最大值 |
FLOAT | 4 | - | - |
DOUBLE | 8 | - | - |
定点数类型 | 字节 | 最小值 | 最大值 |
DEC(M,D) DECIMAL(M,D) | M+2 | - | - |
位类型 | 字节 | 最小值 | 最大值 |
BIT(M) | 1~8 | BIT(1) | BIT(8) |
1.1、整数类型
在整数类型中,按照取值范围和存储方式的不同,分为 tinyint、smallint、mediumint、int 和 bigint 这 5 个类型。如果超出类型范围的操作,会发生 “Out of range” 错误提示。
对于整数类型,MySQL 还支持在类型名称后面的小括号内指定显示宽度,例如 int(5) 表示当数值宽度小于 5 位的时候在数字前面填满宽度,如果不显示指定宽度则默认是 int(11)。一般配合 zerofull 使用,就是用 “0” 填充。举例:
1、创建表 t1,有 id1 和 id2 两个字段,指定其数值宽度分别为 int 和 int(5):
mysql> create table t1 (id1 int,id2 int(5));
Query OK, 0 rows affected (0.01 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.02 sec)
2、id1 和 id2 都插入数值 1,没有异常:
mysql> insert into t1 values (1,1); #id1 和 id2 都插入数值1,没有异常
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
3、分别修改 id1 和 id2 的字段类型,加入 zerofill 参数:
mysql> alter table t1 modify id1 int zerofill;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify id2 int(5) zerofill;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------------+-------+
| id1 | id2 |
+------------+-------+
| 0000000001 | 00001 |
+------------+-------+
1 row in set (0.00 sec)
那如果插入大于宽度限制的值,会不会截断或者插不进去报错?答案是肯定的:不会对插入的数据有任何影响,还是按照类型的实际精度进行保存,这时,宽度格式实际已经没有意义,左边不会再填充任何的 “0” 字符。
4、在表 t1 的字段 id1 插入数值 1,id2 插入数值 1111111,位数为 7,大于 id2 的显示位数 5:
mysql> insert into t1 values(1, 1111111);
Query OK, 1 row affected (0.09 sec)
mysql> select * from t1;
+------------+---------+
| id1 | id2 |
+------------+---------+
| 0000000001 | 1111111 |
+------------+---------+
1 row in set (0.00 sec)
很显然,如上面所说,id2 中显示了正确的数值,并没有受到宽度限制的影响。
所有的整数类型都有一个可选属性 UNSIGNED(无符号),如果需要在字段里面保存非负数或者需要较大的上限值时,可以用此选项。它的取值范围是正常值的下限取 0,上限取原值的 2 倍。如果一个列指定为 zerofill,则 MySQL 自动为该列添加 UNSIGNED 属性。
整数类型还有一个属性:AUTO_INCREMENT。在需要产生唯一属性标识符或顺序值时,可利用此属性,这个属性只用于整数类型。AUTO_INCREMENT 值一般从 1 开始,每行增加 1。一个表中最多只能有一个 AUTO_INCREMENT 列。对于任何想成为 AUTO_INCREMENT 的列,应该设置为 NOT NULL,并定义为 PRIMARY KEY 或定义为 UNIQUE 键。可按照下列方式定义 AUTO_INCREMENT 列:
mysql> create table AI (ID int auto_increment NOT NULL Primary key);
Query OK, 0 rows affected (0.00 sec)
#create table AI (ID int auto_increment NOT NULL,Primary key(ID));
#create table AI (ID int auto_increment NOT NULL,unique(ID));
mysql> desc AI;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)
1.2、小数类型
对于小数的表示,MySQL 分为两种方式:浮点数和定点数。浮点数包括 flost(单精度)和 double(双精度),而定点数则只有 decimal 一种表示。定点数在 MySQL 中采用字符串形式存放,比浮点数更精确,适合用来存放货币等精度高的数据。
浮点数和定点数都可以用类型名称加上 “(M,D)” 的方式来进行表达,"(M,D)" 表示该值一共显示 M 位数字(整数位+小数位),其中 D 位位于小数点后面有几位,M 和 D 又称为精度和标度。值得注意的是:浮点数后面跟 “(M,D)” 是非标准用法,如果要用于数据库迁移,则最好不要这么使用。float 和 double 在不指定精度时,默认会按照实际的精度(由实际的硬件和操作系统决定)来显示,而 decimal 在不指定精度时,默认的整数位为 10,默认的小数位为 0。
通过一个例子来比较 float、double 和 decimal 三者之间的不同:
1、创建表,分别将 id1,id2,id3 字段设置为 float(5,2),double(5,2),decimal(5,2):
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.01 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.00 sec)
2、往 id1,id2,id3 这 3 个字段插入数据 1.23:
mysql> insert into t2 values(1.23,1.23,1.23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00 sec)
3、再向 id1 和 id2 字段中插入数据 1.234,而 id3 中插入 1.23:
mysql> insert into t2 values(1.234,1.234,1.23);
Query OK, 1 row affected (0.00 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)
可以发现,id1 和 id2 由于标度的原因,舍弃了一位。
4、将id1,id2,id3 字段的精度和标度都去掉,再次插入 1.23:
mysql> alter table t2 modify id1 float;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t2 modify id2 double;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t2 modify id3 decimal;
Query OK, 3 rows affected, 3 warnings (0.00 sec)
mysql> 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.00 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 |
+-------+-------+------+
4 rows in set (0.00 sec)
浮点数如果不写精度和标度,则会按照实际精度值显示。如果有精度和标度,则会自动将四舍五入后的结果插入,系统不会报错;定点数如果不写精度和标度,则会按照默认值 decimal(10,0) 来进行操作,并且如果数据超过了精度和标度值,系统会报错。
1.3、BIT(位)类型
对于 BIT(位)类型,用于存放位字段值,BIT(M) 可以用来存放多位二进制数,M 的范围从 1~64,如果不写则默认为 1 位。对于位字段,直接使用 SELECT 命令将看不到结果,可以用 bin() 或者 hex() 函数读取。
mysql> create table t3 (id BIT);
Query OK, 0 rows affected (0.00 sec)
mysql> desc t3;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | bit(1) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t3 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+
| id |
+------+
| |
+------+
1 row in set (0.00 sec)
可以发现,直接 select * 的结果为 NULL。改用 bin() 和 hex() 函数试试:
mysql> select bin(id),hex(id) from t3;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1 | 1 |
+---------+---------+
1 row in set (0.00 sec)
数据插入 bit 类型字段时,首先转换为二进制,如果位数允许,将成功插入。否则,将报错。
mysql> insert into t3 values(2);
ERROR 1406 (22001): Data too long for column 'id' at row 1 #因为 2 的二进制为 “10”,两位
2、日期时间类型
日期类型的主要区别如下:
- 如果想用来表示
年月日
,通常用DATE
来表示; - 如果要用来表示
年月日时分秒
,通常用DATETIME
或者TIMESTAMP
来表示; - 如果只用来表示
时分秒
,通常用TIME
来表示; - 如果只是表示
年份
,可以用YEAR
来表示,它比 DATA 占用更少的空间。
日期和时间类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
DATE | 4 | 1000-01-01 | 9999-12-31 |
DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 4 | - | - |
TIME | 3 | -838:59:59 | 838:59:59 |
YEAR | 1 | 1901 | 2155 |
如果经常插入或者更新日期为系统时间,通常建议使用 DATESTAMP 来表示。
1、首先创建表 t ,字段分别为 date,time,datetime 三种日期类型:
mysql> create table t (d date,t time,dt datetime);
Query OK, 0 rows affected (0.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)
2、用 now() 函数插入当前日期:
mysql> insert into t values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2020-08-12 | 08:37:42 | 2020-08-12 08:37:42 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
下面对 TIMESTAMP 类型的特性进行一些测试:
首先看一下 explicit_defaults_for_timestamp 参数的默认值:
mysql> show variables like 'explicit%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF |
+---------------------------------+-------+
1 row in set (0.12 sec)
3、增加一个字段 ts 数据类型为 DATESTAMP:
mysql> alter table t add ts timestamp;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)
默认值为 CURRENT_TIMESTAMP(系统日期)并且设置了 not null 和 on update CURRENT_TIMESTAMP,插入一个 NULL 试试:
mysql> insert into t(ts) values(NULL);
Query OK, 1 row affected (0.00 sec)
mysql> select ts from t;
+---------------------+
| ts |
+---------------------+
| 2020-08-12 08:42:02 |
+---------------------+
2 rows in set (0.00 sec)
果然,ts 中自动插入了系统日期。
注意,MySQL 现在可以允许多列的默认值为 CURRENT_TIMESTAMP:
mysql> alter table t add ts2 timestamp;
ERROR 1067 (42000): Invalid default value for 'ts2'
mysql> alter table t add ts2 timestamp default CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| ts2 | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
如果将 explicit_defaults_for_timestamp 设置为 on,则默认值、not null 和 on update CURRENT_TIMESTAMP 属性都需要手动设置:
mysql> set explicit_defaults_for_timestamp = on;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t2(id timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> desc t2;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id | timestamp | YES | | NULL | |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)
TIMESTAMP 还有一个重要特点,就是跟时区有关。当插入日期时,会先转换为本地时区后存放;而从数据库里边取出来时,也同样需要将日期转换为本地时区后显示。这样,不同时区的用户看到的同一个日期可能是不一样的。
TIMESTAMP 取值范围较小,不适合存储较久远的日期。
TIMESTAMP 和 DATATIME 的表示方法很类似,主要有以下几个区别:
- TIMESTAMP 支持的时间范围较小,而 DATATIME 的取值范围更大。两者都可以设置默认值和 on update CURRENT_TIMESTAMP 属性,使得日期列可以随其他列的更新而自动更新为最新时间;
- TIMESTAMP 增加了控制参数 explicit_defaults_for_timestamp,如果设置为 on,则 TIMESTAMP 需要显式指定默认值和 on update CURRENT_TIMESTAMP 属性;如果设置为 off,则会自动设置默认值为 CURRENT_TIMESTAMP(系统时间)和 on update CURRENT_TIMESTAMP 属性,并且自动设置为 not null;
- 当 explicit_defaults_for_timestamp 设置为 off 时,表中的第一个 TIMESTAMP 列自动设置为系统时间。如果在一个 TIMESTAMP 列中插入 NULL,则该列值将自动设置为当前系统日期和时间。在插入或更新一行但不明确给出 TIMESTAMP 列赋值时也会自动设置该列的值为当前系统日期和时间;
- TIMESTAMP 的插入和查询都受到当前地区的影响,更能反映出实际的日期。而 DATATIME 则只能反映出插入时区当前的时区,其他时区的人查看数据时必然会有误差的。
YEAR 类型更适合用来表示年份,当应用只需要记录年份时,用 YEAR 比 DATE 将更节省空间。
mysql> create table t3(y year);
Query OK, 0 rows affected (0.33 sec)
mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| y | year(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> insert into t3 values(2100);
Query OK, 1 row affected (0.06 sec)
mysql> select * from t3;
+------+
| y |
+------+
| 2100 |
+------+
1 row in set (0.00 sec)
MySQL 以 YYYY 格式检索和显示 YEAR 值,范围是 1901~2155。当使用两位字符串表示年份时,其范围是 “00” 到 “99”。
- “00” 到 “69” 范围的值被转换为 2000~2069 范围的 YEAR 值;
- “70” 到 “99” 范围的值被转换为 1970~1999 范围的 YEAR 值。
什么样的格式才能够正确地插入到对应的日期字段中(下面以 DATETIME):
- YYYY-MM-DD HH:MM:SS 或 YY-MM-DD HH:MM:SS 格式的字符串。允许 “不严格” 语法,即任何标点符号都可以用作日期部分或时间部分的间隔符。对于包括日期部分间隔符的字符串值,如果日和月的值小于 10,不需要指定两位数;同样,对于包括时间部分分隔符的字符串,如果时、分和秒的值小于 10,则不需要指定两位数。
- YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 格式的没有间隔符的字符串,假定字符串对于日期类型是有意义的。
- YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 格式的没有间隔符的数字,假定数字对于日期类型是有意义的。数字值应为 6、8、12 或者 14 位长。如果是 8 位或者 14 位长,则前 4 位数表示年份;如果数字是 6 位或者 12 位长,则前两位表示年。其他数字被解释为仿佛用零填充到了最近的长度。
- 函数返回的结果,其值适合 DATETIME、DATE 或 TIMESTAMP 上下文。例如 now() 或 CURRENT_DATE。
3、字符串类型
字符串类型 | 字节 | 说明 |
---|---|---|
CHAR(M) | M | M 为 0~255 之间的整数 |
VARCHAR(M) | - | M 为 0~65535 之间的整数,值的长度 +1 个字节 |
TINYBLOB | 允许长度 0~255 字节,值的长度 +1 个字节 | |
BLOB | 允许长度 0~65535 字节,值的长度 +2 个字节 | |
MEIUMBLOB | 值的长度 +3 个字节 | |
LONGBLOB | 值的长度 +4 个字节 | |
TINYTEXT | 值的长度 +2 个字节 | |
TEXT | 值的长度 +2 个字节 | |
MEDIUMTEXT | 值的长度 +3 个字节 | |
LONGTEXT | 值的长度 +4 个字节 | |
VARBINARY(M) | 允许 0~M 个字节的变长字节字符串,值的长度 +1 个字节 | |
BINARY(M) | M | 允许 0~M 个字节的变长字节字符串 |
3.1、CHAR 和 VARCHAR 类型
CHAR 和 VARCHAR 很类似,都用来保存 MySQL 中较短的字符串。两者的主要区别在于存储方式的不同:CHAR 列的长度固定为创建表时声明的长度,长度可以为从 0~255 的任何值;而 VARCHAR 列中的值为可变长字符串,长度可以指定为 0~65535 之间的值。在检索时,CHAR 列删除了尾部的空格,而 VARCHAR 则保留了这些空格。
1、创建表 vc,并定义两个字段 “v VARCHAE(4)” 和 “c CHAR(4)”:
mysql> create table vc (v varchar(4),c char(4));
Query OK, 0 rows affected (0.00 sec)
mysql> desc vc;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| v | varchar(4) | YES | | NULL | |
| c | char(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
2、同时插入字符串 “ab“:
mysql> insert into vc values("ab ","ab ");
Query OK, 1 row affected (0.00 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.00 sec)
mysql> select CONCAT(V,'+'),CONCAT(C,'+') from vc;
+---------------+---------------+
| CONCAT(V,'+') | CONCAT(C,'+') |
+---------------+---------------+
| ab + | ab+ |
+---------------+---------------+
1 row in set (0.00 sec)
行为 | char 字段 | varchar 字段 |
---|---|---|
最大长度 | 255 字符 | 65535 个字节,所以括号中最大的字符数还得通过编码来算 |
是否定长 | 定长,不足的部分用隐藏空格填充 | 不定长 |
空间使用 | 会有浪费 | 更加节省 |
查找效率 | 高 | 低 |
尾部空格 | 插入时省略 | 插入时不会省略,查找时省略 |
like 查找 | 语句中 like 后的 ’ ’ 不会省 | 语句中 like 后的 ’ ’ 不会省,字段结尾的空格也不会省 |
总结:
char(n)
中的 n 是字符数,范围是 0~255(额外需要 1 到 2 个字节来存长度);varchar(n)
中的 n 也是字符数,但是最大值需要通过编码来算,不能超过 65535 字节(从中还需要拿出 1 到 2 个字节来存长度);- 一般定长的数据选用
char
类型,比如身份证号,手机号,电话等,长度变化很大的可以使用varchar
类型; - 注意尾部空格的匹配,特别是插入时和使用 like 查找时。
3.2、BINARY 和 VARBINARY 类型
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不包含非二进制字符串。
在下面的例子中,对表 t4 中的 binary 字段 c 插入一个字符,研究一下这个字符到底是如何存储的。
1、创建测试表 t4,字段为 c BINARY(4):
mysql> create table t4(c BINARY(3));
Query OK, 0 rows affected (0.02 sec)
mysql> desc t4;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| c | binary(3) | YES | | NULL | |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)
2、往 c 字段中插入字符 “a”:
mysql> insert into t4 values('a');
Query OK, 1 row affected (0.00 sec)
3、分别以不同的模式查看 c 列的内容:
mysql> select *, hex(c), c='a', c='a\0', c='a\0\0' from t4;
+------+--------+-------+---------+-----------+
| c | hex(c) | c='a' | c='a\0' | c='a\0\0' |
+------+--------+-------+---------+-----------+
| a | 610000 | 0 | 0 | 1 |
+------+--------+-------+---------+-----------+
1 row in set (0.00 sec)
可以发现,当保存 BINARY 值时,在值的最后通过填充 “0x00”(零字节)以达到指定的字段定义长度。上例中,对于一个 BINARY(3) 列,当插入 “a” 时,变为了 “a\0\0”。
3.3、ENUM 类型
枚举类型,它的值需要在创建表的时候通过枚举方式显示指定。对 1~255 个成员的枚举需要 1 个字节存储;对于 255~65535 个成员,需要 2 个字节的存储。最多允许 65535 个成员。
1、创建表 e,定义 gender 字段为枚举类型,成员为 “M” 和 “F”:
mysql> create table e (gender ENUM('M','F'));
Query OK, 0 rows affected (0.00 sec)
mysql> desc e;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| gender | enum('M','F') | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)
2、插入 4 条不同的数据:
mysql> insert into e values('M'),('1'),('f'),(NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from e;
+--------+
| gender |
+--------+
| M |
| M |
| F |
| NULL |
+--------+
4 rows in set (0.00 sec)
ENUM 是忽略大小写的,在存储 “M” 和 “F” 时将它们都转成了大写,还可以看出对于插入不在 ENUM 指定范围内的值时,并没有警告,而是插入了 ENUM(‘M’,‘F’) 的第一个值 “M”!
另外,ENUM 类型只允许从值集合中选取单个值,而不能一次取多个值。
3.4、SET 类型
SET 也是一个字符串对象,里边可以包含 0~64 个成员。根据成员的不同,存储上也有所不同。
- 1~8 成员的集合,占 1 个字节;
- 9~16 成员的集合,占 2 个字节;
- 17~24 成员的集合,占 3 个字节;
- 25~32 成员的集合,占 4 个字节;
- 33~64 成员的集合,占 8 个字节。
SET 类型可以包含 0~64 个成员,每 8 个成员占 1 字节。SET 和 ENUM 类型除了在存储不同之外,最主要的区别在于 SET 类型一次还可以选取多个成员,而 ENUM 只能选一个:
mysql> alter table e add col set('a','b','c','d');
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc e;
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| gender | enum('M','F') | YES | | NULL | |
| col | set('a','b','c','d') | YES | | NULL | |
+--------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into e(col) values('a,b'),('a,b,a'),('a,c'),('a'),('c,d,d');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select col from e;
+------+
| col |
+------+
| a,b |
| a,b |
| a,c |
| a |
| c,d |
+------+
9 rows in set (0.00 sec)
请注意,SET 类型不允许重复!而且,对于输入的值只能是在组合范围内的值。
4、JSON 类型
JSON
是 JavaScript Object Notation 的缩写,它是一种数据交换格式。JSON 出现之前,数据交换大多使用 XML 来传递数据。
JSON 类型比字符串类型有如下优势:
- JSON 数据类型会自动校验数据是否为 JSON 格式,如果不是 JSON 格式数据,则会报错;
- MySQL 提供了一组操作 JSON 数据的内置函数,可以方便地提取各类数据,可以修改特定的键值;
- 优化的存储格式,存储在 JSON 列中的 JSON 数据被转换成内部的存储格式,允许快速读取。
简单地来说,JSON 实际就是 JacaScript 的一个子集,支持的数据类型包括 Number、String、Boolean、Null、Array、Object 共 6 种,一个 JSON 中的元素可以是这 6 种类型元素的任意组合。其中 Boolean 使用 true/false 的字面值文本表示;null 使用 null 的文本表示;字符串和日期类型都使用双引号表示;Array 要用中括号表示;Object 保存的 KV 对要用大括号表示,其中的 K 也要使用双括号表示。
1、首先,创建表 t5,列 id1 为 JSON 类型:
mysql> create table t5(id1 JSON);
Query OK, 0 rows affected (0.00 sec)
mysql> desc t5;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id1 | json | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)
2、往表 t5 插入 JSON 格式数据:
mysql> insert into t5 values('{"age":20, "time":"2018-07-14 10:52:00"}');
Query OK, 1 row affected (0.09 sec)
3、通过 JSON_TYPE 函数
可以看到插入的 JSON 数据是哪种类型:
mysql> select json_type('"abc"'), json_type('[1, 2, "abc"]'), json_type('{"k1":"value"}');
+--------------------+----------------------------+-----------------------------+
| json_type('"abc"') | json_type('[1, 2, "abc"]') | json_type('{"k1":"value"}') |
+--------------------+----------------------------+-----------------------------+
| STRING | ARRAY | OBJECT |
+--------------------+----------------------------+-----------------------------+
1 row in set (0.00 sec)
4、JSON 数据类型对于大小写是敏感的,常见的 null、true、false 必须小写才合法。通过 JSON_VALID 函数
可以判断一个 JSON 数据是否合法:
mysql> select json_valid('null'), json_valid('NULL'), json_valid('false'), json_valid('FALSE');
+--------------------+--------------------+---------------------+---------------------+
| json_valid('null') | json_valid('NULL') | json_valid('false') | json_valid('FALSE') |
+--------------------+--------------------+---------------------+---------------------+
| 1 | 0 | 1 | 0 |
+--------------------+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)
还有一种特殊情况,如果 JSON 数据的 value 中字符串 value 中包括双引号或者单引号,则插入时需要加反斜杠进行转义:
-
显式插入
mysql> insert into t5 values(json_object("name", "ab\"c")); Query OK, 1 row affected (0.00 sec)
-
隐式插入
mysql> insert into t5 values('{"name":"ab\"c"}'); ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 12 in value for column 't5.id1'. mysql> insert into t5 values('{"name":"ab\\"c"}'); Query OK, 1 row affected (0.00 sec)
可以发现,隐式插入时,要多加一个反斜杠才能正常识别。
MySQL 对 JSON 的存储做了一些限制,JSON 列不可有默认值,且文本的最大长度取决于系统常量:max_allowed_packet。