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支持的数据类型
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类型
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中float、double、decimal三个浮点类型的区别与总结!
极客小俊
08-14 6740
那么多浮点用哪个？
ftoa 正负数都可以用哦
Yan_Programer 的专栏
04-18 929
#include #define ARRAY_MAX 20 /* *dnum   input float number *str        input char array *len       input float length * */ char *ftoa(float dnum,char *str,int len)
mysql从头学一 1.0数据类型-数值类型
insis_mo的博客
10-31 1286
数值类型 MySQL 支持所有标准 SQL 中的数值类型，其中包括严格数值类型（INTEGER、SMALLINT、 DECIMAL 和 NUMERIC），以及近似数值数据类型（FLOAT、REAL 和 DOUBLE PRECISION），并在此基础上做了扩展。扩展后增加了 TINYINT、MEDIUMINT 和 BIGINT 这 3 种长度不同的整型，并增加了 BIT 类型，用来存放位数据。表...
MySQL学习笔记(2)——索引
12-14
MySQL中的索引是一种为了加速数据检索而创建的数据结构，它基于B树模型，能显著提升查询效率，尤其是在大型数据集中。索引的主要目的是减少数据库在查找特定数据时需要扫描的数据量，从而加快查询速度，同时也可支持...
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 数据类型之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数据类型
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...
MySQL基础之----数据类型篇(常用数据类型)
weixin_45591980的博客
10-03 219
MySQL数据类型 注: 转载 https://www.runoob.com/mysql/mysql-data-types.html MySQL中定义数据字段的类型对数据库的优化是非常有效的 MySQL支持多种类型, 大致可以分为三类: 数值, 日期/时间和字符串(字符)类型 注: 本文全部使用小写形式, SQL语句不区分大小写命令 数值类型 MySQL 支持所有标准SQL数值数据类型 这些类型包括严格数值数据类型(integer, smallint, decimal和numeric) , 以及浮点型
MySQL （入门篇03）MySQL数据类型 和 常用字段属性总结
半亩方糖
05-16 452
1. 数值 类型 含义 大小 tinyint 小数据 1字节 smallint 较小数据 2字节 mediumint 中等数据 3字节 int 最常用的标准数据 4字节 bigint 大的数据 8字节 float 浮点数 4字节 double 双精度浮点数 8字节 decimal 字符串形式浮点数，金融使用 2. 字符串 类型 含义 大小 char 固定大小的字符串 0-255字节 varchar 改变字符（最常用） 2字节 tiny
字段类型 mysql_mysql 常用字段类型
weixin_36415206的博客
02-11 77
tinyint[(m)] [unsigned] [zerofill] 1字节极小整数，数据类型用于保存一些范围的整数数值范围：有符号：-128 ～ 127.无符号：～ 255特别的： MySQL中无布尔值，使用tinyint(1)构造。smallint 2字节小整数有符号：~ 32768无符号：~ 65535mediumint...
尚硅谷MySQL高级学习笔记：存储引擎、SQL优化与索引详解
本资源是一份尚硅谷MySQL高级视频学习笔记，涵盖了多个关键知识点，旨在帮助用户深入理解并掌握MySQL高级操作。以下是对部分章节的详细解读： 1. Linux下MySQL编码设置：当在Linux环境下修改MySQL的字符集编码时，...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值