MySQL基础学习笔记之——MySQL支持的数据类型

最新推荐文章于 2022-06-13 20:24:45 发布
最新推荐文章于 2022-06-13 20:24:45 发布
阅读量494 收藏 1
点赞数 1
分类专栏： MySQL数据库基础+高级 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36879493/article/details/107949586
版权

MySQL支持的数据类型

1、数值类型

整数类型字节最小值最大值
TINYINT1有符号：-128
无符号：0		有符号：127
无符号：255
SMALLINT2有符号：-32768
无符号：0		有符号：32767
有符号：65535
MEDIUMINT3有符号：-8388608
无符号：0		有符号：8388607
无符号：1677215
INT、INTEFRE4有符号：-2147483648
无符号：0		有符号：2147483647
无符号：4294967295
BIGINT8--
浮点数类型字节最小值最大值
FLOAT4--
DOUBLE8--
定点数类型字节最小值最大值
DEC(M,D)
DECIMAL(M,D)		M+2--
位类型字节最小值最大值
BIT(M)1~8BIT(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 占用更少的空间。
日期和时间类型字节最小值最大值
DATE41000-01-019999-12-31
DATETIME81000-01-01 00:00:009999-12-31 23:59:59
TIMESTAMP4--
TIME3-838:59:59838:59:59
YEAR119012155

如果经常插入或者更新日期为系统时间，通常建议使用 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)MM 为 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 后的 ’ ’ 不会省，字段结尾的空格也不会省

总结：

  1. char(n)中的 n 是字符数，范围是 0~255（额外需要 1 到 2 个字节来存长度）；
  2. varchar(n) 中的 n 也是字符数，但是最大值需要通过编码来算，不能超过 65535 字节（从中还需要拿出 1 到 2 个字节来存长度）；
  3. 一般定长的数据选用 char 类型，比如身份证号，手机号，电话等，长度变化很大的可以使用 varchar 类型；
  4. 注意尾部空格的匹配，特别是插入时和使用 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。

前丨尘忆·梦
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL学习笔记(2)——索引
12-14
MySQL中的索引是一种为了加速数据检索而创建的数据结构，它基于B树模型，能显著提升查询效率，尤其是在大型数据集中。索引的主要目的是减少数据库在查找特定数据时需要扫描的数据量，从而加快查询速度，同时也可支持...
MySQL中float、double、decimal三个浮点类型的区别与总结!
极客小俊
08-14 6765
那么多浮点用哪个？
mysql从头学一 1.0数据类型-数值类型
insis_mo的博客
10-31 1286
数值类型 MySQL 支持所有标准 SQL 中的数值类型，其中包括严格数值类型（INTEGER、SMALLINT、 DECIMAL 和 NUMERIC），以及近似数值数据类型（FLOAT、REAL 和 DOUBLE PRECISION），并在此基础上做了扩展。扩展后增加了 TINYINT、MEDIUMINT 和 BIGINT 这 3 种长度不同的整型，并增加了 BIT 类型，用来存放位数据。表...
【数据库】——MySQL支持的数据类型
qq_43412060的博客
03-11 322
1、数值类型 1.1zerofill——用“0”填充 1.2小数的表示——float,double,decimal 2、日期时间类型 2.1data、time.datatime三种日期类型区别 2.2timestamp和datatime区别 2.3日期正确的插入格式 3、字符串类型 3.1char和varchar 3.2 binary和varbinary类型 3.3enum和set类型
Mysql 数据类型之zerofill使用（一）
civbl2961的博客
12-28 345
用过数据库的朋友都知道，要想将数据存入表中，需要将表建立多个字段，对于这些字段要有合理的类型来匹配，因为是初学Mysql，所以在此做些小的总结与记录，以便自己以后学习。 数值类型 ...
SQL
qq_28299831的博客
10-13 226
create table t1(id1 int ,id2 int(5)); desc t1; 显示表结构 insert into t1 values(1,1); select * from t1; 输出整个表 分别修改id1，id2字段类型，加入zerofill参数(用零填充数据类型) alter table t1 modify id1 int zerofill; 自增列的定义，a
mysql的学习笔记——mysql的核心概念、使用到哪些技术、以及实现。.zip
最新发布
02-22
MySQL是世界上最受欢迎的关系型数据库...以上就是MySQL学习笔记中的关键知识点，涵盖从基本概念到高级技术，为理解MySQL的工作原理和实际应用提供了坚实的基础。通过深入学习和实践，可以成为熟练掌握MySQL的专业人士。
读书笔记系列1——MySQL必知必会.pdf
12-07
- **模式**：描述数据库和表的结构及特性，包括数据类型、列定义和约束。 - **列**：表中的字段，存储单个信息，如顾客编号、姓名等。 - **行（记录）**：表中的水平单元，存储表中的一条完整数据。 - **主键**...
Mysql学习笔记、java开发
07-06
MySQL是世界上最流行的开源关系型数据库管理系统之一，广泛用于各种规模的应用程序，特别是Web应用程序。它以其易用性、高效性和可扩展性而受到开发者青睐。本文将深入探讨MySQL中的核心概念，包括查询、连接操作、...
MySQL基础笔记
12-14
总之，MySQL的基础知识包括了数据库的概念、类型、操作、数据类型和字段属性等方面。深入理解这些概念和技术将有助于开发和维护高效的数据存储系统，无论是在学术研究还是在实际工作中，都是IT专业人士必不可少的...
MySQL数据类型
clyu的博客
04-03 322
1、整数型 类型 字节数 取值范围 tinyint 1字节 有符号值：-128到127(-27到27-1) 无符号值：0到255（0到2^8-1) smallint 2字节 有符号值：-32768到32766（-215到215 -1） 无符号值: 0到65535 （0到2^16 -1） mediumint 3字节 有符号值：-8388608到8388607（-223到223-1） 无符号值: 0到16777215（0到2^24-1） int 4字节 有符号值：-21
MySQL基础知识复习（创建数据表时的：主键约束、唯一性约束、非空约束、修改字段和数据类型、添加新字段、删除字段、设置初始值、唯一性索引、组合索引）
xiaomoge__的博客
06-13 1111
创建数据表时的：主键约束、唯一性约束、非空约束、修改字段和数据类型、添加新字段、删除字段、设置初始值、唯一性索引、组合索引
07_常见的数据类型
qq1075666462的博客
11-26 256
常见的数据类型内容如下数值类型字符类型日期类型 数值类型 整型 整数类型 字节 范围 Tinyint 1 有符号：-128~127 无符号：0~255 Smallint 2 有符号：-32768~32767 无符号：0~65535 Mediumint 3 有符号：-8388608~8388607 无符号：0~1677215 Int 、integer 4 有符号：- 2147483648~2147483647 无符号：0~4294967295 Bigint 8 有符号：-92
mysql数据库数字类型_Mysql 数据库数值类型详解
weixin_32279911的博客
01-19 1978
MySQL 支持所有标准SQL 中的数值类型，其中包括严格数值类型(INTEGER、SMALLINT、DECIMAL 和NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和DOUBLE PRECISION)，并在此基础上做了扩展。扩展后增加了TINYINT、MEDIUMINT 和BIGINT 这3 种长度不同的整型，并增加了BIT 类型，用来存放位数据。表3-1 中列出了MySQL 5...
C#链接mysql出现 One of the identified items was in an invalid format
weixin_30410999的博客
08-01 2277
这个问题在tolist查询结果的时候就会出现但是count就不会出现，后来才发现是数据生成工具生成出来的ID有问题导致的，只要保证iD不重复并且按照指定的类型建立ID就可以了 转载于:https://www.cnblogs.com/llcdbk/p/11281473.html...
字段类型 mysql_mysql 常用字段类型
weixin_36415206的博客
02-11 77
tinyint[(m)] [unsigned] [zerofill] 1字节极小整数，数据类型用于保存一些范围的整数数值范围：有符号：-128 ～ 127.无符号：～ 255特别的： MySQL中无布尔值，使用tinyint(1)构造。smallint 2字节小整数有符号：~ 32768无符号：~ 65535mediumint...
mysql 0或1用什么类型_01-mysql中的数据类型
weixin_42363874的博客
01-19 4124
mysql中的列数据类型：数值型、字符串类型、日期/时间类型3种几种列类型描述使用了下述惯例：· M#表示最大显示宽度。最大有效显示宽度是255。· D#适用于浮点和定点类型，表示小数点后面的位数。最大可能的值是30，但不应大于M-2。· [] #方括号(‘[’和‘]’)表示可选部分。1、 数值类型严格数值类型(INTEGER、SMALLINT、DECIMAL和NUMERIC...
mysql 主属性 参照_mysql类型参照
weixin_42641741的博客
01-27 477
mysql数据库内容总结数字类型：Tinyint(M) unsigned zerofull1个字节 默认的是有符号的-128~127unsigned: 无符号整数 范围0~255M: 代表宽度 (在zerofull时才有意义)，只是一个显示效果，不会影响到实际的数据长度zerofull: 零填充(如果某列是zerofull默认就是unsigned) 因为不会有 0000-1 的情况零填充(例: 0...
尚硅谷MySQL高级学习笔记：存储引擎、SQL优化与索引详解
本资源是一份尚硅谷MySQL高级视频学习笔记，涵盖了多个关键知识点，旨在帮助用户深入理解并掌握MySQL高级操作。以下是对部分章节的详细解读： 1. Linux下MySQL编码设置：当在Linux环境下修改MySQL的字符集编码时，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值