MySQL数据类型
2022/04/10
MySQL
> 视频教程：[尚硅谷_MySQL](http://www.atguigu.com/download_detail.shtml?v=349) 官方文档：[Chapter 11 Data Types](https://dev.mysql.com/doc/refman/8.0/en/data-types.html) 下文示例以 `5.7.25` 为例 # 简介 类型 | 类型举例 --- | --- 整数类型 | TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT 浮点类型 | FLOAT、DOUBLE 定点数类型 | DECIMAL 日期时间类型 | YEAR、TIME、DATE、DATETIME、TIMESTAMP 文本字符串类型 | CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT 二进制字符串类型 | BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB 枚举类型 | ENUM 集合类型 | SET JSON类型 | JSON对象、JSON数组 位类型 | BIT 空间数据类型 | 单值类型：GEOMETRY、POINT、LINESTRING、POLYGON<br>集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION 常见数据类型的属性，如下： MySQL关键字 | 含义 --- | --- NULL | 数据列可包含NULL值 NOT NULL | 数据列不允许包含NULL值 DEFAULT | 默认值 PRIMARY KEY | 主键 AUTO_INCREMENT | 自动递增，适用于整数类型 UNSIGNED | 无符号 CHARACTER SET name | 指定一个字符集 # 字符集 ## 查看系统默认字符串 使用 `SHOW VARIABLES` 查看默认字符集 ``` mysql> SHOW VARIABLES LIKE 'character_%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.04 sec) ``` 修改 `MySQL` 配置文件可以设置默认字符集 ``` [mysqld] # 服务端使用的字符集 character-set-server=utf8mb4 ``` ## 创建数据库时指名字符集 ``` mysql> CREATE DATABASE IF NOT EXISTS dbtest CHARACTER SET 'utf8mb4'; Query OK, 1 row affected (0.00 sec) mysql> SHOW CREATE DATABASE dbtest; +----------+--------------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------------+ | dbtest | CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ | +----------+--------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` ## 创建表的时候，指名表的字符集 ``` mysql> CREATE TABLE temp(id INT) CHARACTER SET 'utf8mb4'; Query OK, 0 rows affected (0.01 sec) mysql> SHOW CREATE TABLE temp; +-------+-------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------+ | temp | CREATE TABLE `temp` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+-------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` ## 创建表中的字段时，可以指定字段的字符集 ``` mysql> CREATE TABLE temp1(`name` VARCHAR(15) CHARACTER SET 'utf8mb4'); Query OK, 0 rows affected (0.01 sec) mysql> SHOW CREATE TABLE temp1; +-------+--------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------+ | temp1 | CREATE TABLE `temp1` ( `name` varchar(15) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+--------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` # 整数 ## 取值范围 类型 | 字节 | 最小值（有符号） | 最大值（有符号） | 显示宽度（有符号） | 最小值（无符号） | 最大值（无符号） | 显示宽度（无符号） --- | --- | --- | --- | --- | --- | --- | --- TINYINT | 1 | -128 | 127 | 4 | 0 | 255 | 3 SMALLINT | 2 | -32768 | 32767 | 6 | 0 | 65535 | 5 MEDIUMINT | 3 | -8388608 | 8388607 | 9 | 0 | 16777215 | 8 INT、INTEGER | 4 | -2^31<br>-2147483648 | 2^31 - 1<br>2147483647 | 11 | 0 | 2^32 - 1<br>4294967295 | 10 BIGINT | 8 | -2^63<br>-9223372036854775808 | 2^63 - 1<br>9223372036854775807 | 20 | 0 | 2^64 - 1<br>18446744073709551616 | 20 ## 可选属性 整数类型的可选属性有三个： > 无符号属性 `UNSIGNED`： 所有的整数类型都有一个可选的属性 `UNSIGNED`（无符号属性），无符号整数类型的最小取值为 0 。所以，如果需要在 `MySQL` 数据库中保存非负整数值时，可以将整数类型设置为无符号类型。 ```sql CREATE TABLE test ( f1 INT UNSIGNED ); ``` > 显示宽度`M`： `M`的取值范围是`(0,255)`。例如：`int(5)`，当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合`ZEROFILL`使用，表示用`0`填满宽度，否则指定显示宽度无效。 如果设置了显示宽度，**不会**对插入的数据有任何影响，还是按照类型的实际宽度进行保存，即显示宽度与类型可以存储的值范围无关。从`MySQL8.0.19`开始，显示宽度属性仅在同时设置`ZEROFILL`时有效。 > 0填充`ZEROFILL`： - 如果指定了 `ZEROFILL` ，只是表示不够 `M` 位时，用 `0` 在左边填充。 - `ZEROFILL` 必须 `UNSIGNED` 一起使用，即不能使用负数 - 如果数值超过 `M` 位，就按照实际位数存储。无须再用字符 `0` 进行填充。 > 示例 ``` mysql> CREATE TABLE test_num ( f1 INT, f2 INT ( 5 ), f3 INT ( 5 ) ZEROFILL ); Query OK, 0 rows affected, 3 warnings (0.01 sec) mysql> DESC test_num; +-------+--------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------------------+------+-----+---------+-------+ | f1 | int(11) | YES | | NULL | | | f2 | int(5) | YES | | NULL | | | f3 | int(5) unsigned zerofill | YES | | NULL | | +-------+--------------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> INSERT INTO test_num ( f1, f2, f3 ) VALUES ( 123, 123, 123 ),( 123456, 123456, 123456 ); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test_num; +--------+--------+--------+ | f1 | f2 | f3 | +--------+--------+--------+ | 123 | 123 | 00123 | | 123456 | 123456 | 123456 | +--------+--------+--------+ 2 rows in set (0.00 sec) ``` # 浮点数 > 从 `MySQL 8.0.17` 开始，`FLOAT` 和 `DOUBLE` 在官方文档中已经明确不推荐使用 浮点数和定点数类型的特点是可以处理小数，可以把整数看成小数的一个特例。因此，浮点数和定点数的使用场景，比整数大多了。`MySQL` 支持的浮点数类型，分别是`FLOAT`、`DOUBLE`。`FLOAT`占用4字节，取值范围小；`DOUBLE`占用8字节，取值范围大。 - `FLOAT` 表示单精度浮点数； - `DOUBLE` 表示双精度浮点数； 类型 | 字节数 | 最小值 | 最大值 --- | --- | --- | --- FLOAT | 4 | -3.402823466E+38 | 3.402823466E+38 DOUBLE | 8 | -1.7976931348623157E+308 | 1.7976931348623157E+308 # 定点数 类型 | 字节数 | 含义 --- | --- | --- DECIMAL(M,D) | M+2字节 | 有效范围由M和D决定 使用 `DECIMAL(M,D)` 方式表示高精度小数。其中，`M` 被称为精度，`D` 被称为标度。`0<=M<=65 , 0<=D<=30 , D<M`。例如，定义 `DECIMAL(5,2)` 的类型，表示该列取值范围是 -999.99~999.99`。定点数在 `MySQL` 内部是以**字符串**的形式进行存储，这就决定了它一定是精准的。当 `DECIMAL` 类型不指定精度和标度时，其默认为 `DECIMAL(10,0)`。当数据的精度超出了定点数类型的精度范围时，则 `MySQL` 同样会进行四舍五入处理。 > 示例 ``` mysql> CREATE TABLE test_decimal ( f1 DECIMAL, f2 DECIMAL ( 5, 2 ) ); Query OK, 0 rows affected (0.02 sec) mysql> DESC test_decimal; +-------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | f1 | decimal(10,0) | YES | | NULL | | | f2 | decimal(5,2) | YES | | NULL | | +-------+---------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> INSERT INTO test_decimal ( f1, f2 ) VALUES ( 123, 999.99 ),( 123.45, 67.456 ); Query OK, 2 rows affected, 2 warnings (0.01 sec) Records: 2 Duplicates: 0 Warnings: 2 mysql> SELECT * FROM test_decimal; +------+--------+ | f1 | f2 | +------+--------+ | 123 | 999.99 | | 123 | 67.46 | +------+--------+ 2 rows in set (0.00 sec) ``` # 日期 ## 简介 日期与时间是重要的信息，在系统中，几乎所有的数据表都用得到。原因是客户需要知道数据的时间标签，从而进行数据查询、统计和处理。`MySQL` 有多种表示日期和时间的数据类型，不同的版本可能有所差异，`MySQL8.0` 版本支持的日期和时间类型主要有： - `YEAR` 类型通常用来表示年 - `DATE` 类型通常用来表示年、月、日 - `TIME` 类型通常用来表示时、分、秒 - `DATETIME` 类型通常用来表示年、月、日、时、分、秒 - `TIMESTAMP` 类型通常用来表示带时区的年、月、日、时、分、秒 类型 | 名称 | 字节 | 日期格式 | 最小值 | 最大值 --- | --- | --- | --- | --- | --- YEAR | 年 | 1 | YYYY或YY | 1901 | 2155 DATE | 日期 | 3 | YYYY-MM-DD | 1000-01-01 | 9999-12-03 TIME | 时间 | 3 | HH:MM:SS | -838:59:59 | 838:59:59 DATETIME | 日期时间 | 8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 TIMESTAMP | 日期时间 | 4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC | 2038-01-19 03:14:07 UTC > `TIME` 类型，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。 ## YEAR 年 `YEAR` 有以下几种存储格式： - 以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。 - 以2位字符串格式表示YEAR类型，最小值为00，最大值为99。 - 当取值为01到69时，表示2001到2069； - 当取值为70到99时，表示1970到1999； - 当取值整数的0或00添加的话，那么是0000年； - 当取值是日期/字符串的'0'添加的话，是2000年。 从 `MySQL5.5.27` 开始，2位格式的 `YEAR` 已经不推荐使用。`YEAR` 默认格式就是 `YYYY` ，没必要写成 `YEAR(4)` ，从 `MySQL8.0.19` 开始，不推荐使用指定显示宽度的 `YEAR(4)` 数据类型。 > 示例 ``` mysql> CREATE TABLE test_year ( f1 YEAR, f2 YEAR ( 4 ) ); Query OK, 0 rows affected (0.01 sec) mysql> DESC test_year; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | f1 | year(4) | YES | | NULL | | | f2 | year(4) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.04 sec) mysql> INSERT INTO test_year VALUES ( '2021', 2022 ),( '45', '71' ),( 0, '0' ); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test_year; +------+------+ | f1 | f2 | +------+------+ | 2021 | 2022 | | 2045 | 1971 | | 0000 | 2000 | +------+------+ 3 rows in set (0.00 sec) ``` ## DATE 日期 `DATE` 类型表示日期，没有时间部分，格式为 `YYYY-MM-DD` ，其中，`YYYY` 表示年份，`MM` 表示月份，`DD` 表示日期。需要3个字节的存储空间。在向 `DATE` 类型的字段插入数据时，同样需要满足一定的格式条件。 - 以 `YYYY-MM-DD` 格式或者 `YYYYMMDD` 格式表示的字符串日期，其最小取值为 `1000-01-01` ，最大取值为 `9999-12-03` 。 `YYYYMMDD` 格式会被转化为 `YYYY-MM-DD` 格式。 - 以 `YY-MM-DD` 格式或者 `YYMMDD` 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足 `YEAR` 类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。 - 使用 `CURRENT_DATE()` 或者 `NOW()` 函数，会插入当前系统的日期。 > 示例 ``` mysql> CREATE TABLE test_date ( f1 DATE ); Query OK, 0 rows affected (0.01 sec) mysql> DESC test_date; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | f1 | date | YES | | NULL | | +-------+------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> INSERT INTO test_date VALUES ( '2020-10-01' ), ( '20201001' ), ( 20201001 ), ( CURDATE() ), ( CURRENT_DATE() ), ( NOW() ); Query OK, 6 rows affected, 1 warning (0.00 sec) Records: 6 Duplicates: 0 Warnings: 1 mysql> SELECT * FROM test_date; +------------+ | f1 | +------------+ | 2020-10-01 | | 2020-10-01 | | 2020-10-01 | | 2022-04-10 | | 2022-04-10 | | 2022-04-10 | +------------+ 6 rows in set (0.01 sec) ``` ## TIME 时间 `TIME` 类型用来表示时间，不包含日期部分，需要3个字节的存储空间，可以使用 `HH:MM:SS` 格式来表示 `TIME` 类型，其中，`HH` 表示小时，`MM` 表示分钟，`SS` 表示秒。 `TIME` 类型的字段插入数据时，也可以使用几种不同的格式。 - 可以使用带有冒号的字符串，比如 `'D HH:MM:SS'` 、 `'HH:MM:SS'` 、 `'HH:MM'` 、 `'D HH:MM'` 、 `'D HH'` 或 `'SS'` 格式，都能被正确地插入。其中 `D` 表示天，其最小值为0，最大值为34。如果使用带有 `D` 格式的字符串时，`D` 会被转化为小时，计算格式为 `D*24+HH` 。当使用带有冒号并且不带 `D` 的字符串表示时间时，表示当天的时间，比如 `12:10`表示 `12:10:00` ，而不是 `00:12:10`。 - 可以使用不带有冒号的字符串或者数字，格式为 `'HHMMSS'` 或者 `HHMMSS` 。如果插入一个不合法的字符串或者数字，会将其自动转化为 `00:00:00` 进行存储。比如 `1210` 表示 `00:12:10` 。 - 使用 `CURRENT_TIME()` 或者 `NOW()` ，会插入当前系统的时间。 > 示例 ``` mysql> CREATE TABLE test_time ( f1 TIME ); Query OK, 0 rows affected (0.01 sec) mysql> DESC test_time; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | f1 | time | YES | | NULL | | +-------+------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> INSERT INTO test_time VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_time VALUES ('123520'), (124011),(1210); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_time VALUES (NOW()), (CURRENT_TIME()),(CURTIME()); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test_time; +----------+ | f1 | +----------+ | 60:30:29 | | 12:35:29 | | 12:40:00 | | 60:40:00 | | 29:00:00 | | 00:00:45 | | 12:35:20 | | 12:40:11 | | 00:12:10 | | 18:38:20 | | 18:38:20 | | 18:38:20 | +----------+ 12 rows in set (0.00 sec) ``` ## DATETIME 日期时间 `DATETIME` 类型在所有的日期时间类型中占用的存储空间最大，总共需要8个字节的存储空间。在格式上为 `DATE` 类型和 `TIME` 类型的组合，可以表示为 `YYYY-MM-DD HH:MM:SS` ，其中 `YYYY` 表示年份， `MM` 表示月份， `DD` 表示日期， `HH` 表示小时， `MM` 表示分钟， `SS` 表示秒。 `DATETIME` 类型的字段插入数据时，同样需要满足一定的格式条件。 - 以 `YYYY-MM-DD HH:MM:SS` 格式或者 `YYYYMMDDHHMMSS` 格式的字符串插入时，最小值为 `1000-01-01 00:00:00`，最大值为`9999-12-03 23:59:59`。以 `YYYYMMDDHHMMSS` 格式的数字插入时，会被转化为 `YYYY-MM-DD HH:MM:SS`。 - 以 `YY-MM-DD HH:MM:SS` 格式或者 `YYMMDDHHMMSS` 格式的字符串插入时，两位数的年份规则符合 `YEAR` 类型的规则，00到69表示2000到2069；70到99表示1970到1999。 - 使用函数 `CURRENT_TIMESTAMP()` 和 `NOW()` ，可以向 `DATETIME` 类型的字段插入系统的当前日期和时间。 ``` mysql> CREATE TABLE test_datetime ( dt DATETIME ); Query OK, 0 rows affected (0.01 sec) mysql> DESC test_datetime; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | dt | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> INSERT INTO test_datetime VALUES ('2021-01-01 06:50:30'), ('20210101065030'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_datetime VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'), ('200101000000'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_datetime VALUES (20200101000000), (200101000000), (19990101000000), (990101000000); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_datetime VALUES (CURRENT_TIMESTAMP()), (NOW()),(SYSDATE()); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test_datetime; +---------------------+ | dt | +---------------------+ | 2021-01-01 06:50:30 | | 2021-01-01 06:50:30 | | 1999-01-01 00:00:00 | | 1999-01-01 00:00:00 | | 2020-01-01 00:00:00 | | 2020-01-01 00:00:00 | | 2020-01-01 00:00:00 | | 2020-01-01 00:00:00 | | 1999-01-01 00:00:00 | | 1999-01-01 00:00:00 | | 2022-04-10 18:49:21 | | 2022-04-10 18:49:21 | | 2022-04-10 18:49:21 | +---------------------+ 13 rows in set (0.00 sec) ``` ## TIMESTAMP 时间戳 `TIMESTAMP` 类型也可以表示日期时间，其显示格式与 `DATETIME` 类型相同，都是 `YYYY-MM-DD HH:MM:SS` ，需要4个字节的存储空间。但是 `TIMESTAMP` 存储的时间范围比 `DATETIME` 要小很多，只能存储 `1970-01-01 00:00:01 UTC - 2038-01-19 03:14:07 UTC` 之间的时间。其中，`UTC` 表示世界统一时间，也叫作世界标准时间。存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用 `TIMESTAMP` 存储的同一个时间值，在不同的时区查询时会显示不同的时间。插入数据时，当插入的数据格式满足 `YY-MM-DD HH:MM:SS` 和 `YYMMDDHHMMSS` 时，两位数值的年份同样符合 `YEAR` 类型的规则条件，只不过表示的时间范围要小很多。如果向 `TIMESTAMP` 类型的字段插入的时间超出了范围，则会抛出错误信息。 > 示例 ``` mysql> CREATE TABLE test_timestamp ( ts TIMESTAMP ); Query OK, 0 rows affected (0.01 sec) mysql> DESC test_timestamp; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO test_timestamp VALUES ('1999-01-01 03:04:50'), ('19990101030405'), ('99-01-01 03:04:05'), ('990101030405'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_timestamp VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test_timestamp VALUES (CURRENT_TIMESTAMP()), (NOW()); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test_timestamp; +---------------------+ | ts | +---------------------+ | 1999-01-01 03:04:50 | | 1999-01-01 03:04:05 | | 1999-01-01 03:04:05 | | 1999-01-01 03:04:05 | | 2020-01-01 00:00:00 | | 2020-01-01 00:00:00 | | 2022-04-10 20:36:18 | | 2022-04-10 20:36:18 | +---------------------+ 8 rows in set (0.00 sec) ``` `TIMESTAMP` 和 `DATETIME` 的区别： - `TIMESTAMP` 存储空间比较小，表示的日期时间范围也比较小 - 底层存储方式不同， `TIMESTAMP` 底层存储的是毫秒值，距离 `1970-1-1 0:0:0 0` 毫秒的毫秒值。 - 两个日期比较大小或日期计算时， `TIMESTAMP` 更方便、更快。 - `TIMESTAMP` 和时区有关。 `TIMESTAMP` 会根据用户的时区不同，显示不同的结果。而 `DATETIME` 则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。 # 文本 文本字符串总体上分为如下类型。 类型 | 长度 | 长度范围 | 空间（字节） --- | --- | --- | --- CHAR(M) | M | 0 <= M <= 255 | M VARCHAR(M) | M | 0 <= M <= 65535 | M+1 TINYTEXT | L | 0 <= L <= 255 | L+2 TEXT | L | 0 <= L <= 65535 | L+2 MEDIUMTEXT | L | 0 <= L <= 16777215 | L+3 LONGTEXT | L | 0 <= L <= 4294967295 | L+4 ENUM | L | 0 <= L <= 65535 | 1或2 SET | L | 0 <= L <= 64 | 1,2,3,4,8 ## CHAR 与 VARCHAR 字符串 `CHAR` 和 `VARCHAR` 类型都可以存储比较短的字符串。 类型 | 特点 | 长度 | 长度范围 | 占用的存储空间 --- | --- | --- | --- | --- CHAR(M) | 固定长度 | M | 0 <= M <= 255 | M个字节 VARCHAR(M) | 可变长度 | M | 0 <= M <= 65535 | (实际长度 + 1) 个字节 - `CHAR` - `CHAR(M)` 类型一般需要预先定义字符串长度。如果不指定 `(M)` ，则表示长度默认是1个字符。 - 如果保存时，数据的实际长度比 `CHAR` 类型声明的长度小，则会在**右侧填充**空格以达到指定的长度。当MySQL检索 `CHAR` 类型的数据时， `CHAR` 类型的字段会去除尾部的空格。 - 定义 `CHAR` 类型字段时，声明的字段长度即为 `CHAR` 类型字段所占的存储空间的字节数。 - `VARCHAR` - `VARCHAR(M)` 定义时， 必须指定长度 `M` ，否则报错。 - `MySQL4.0` 版本以下， `varchar(20)` 指的是20字节，如果存放 `UTF8` 汉字时，只能存6个（每个汉字3字节） - `MySQL5.0` 版本以上， `varchar(20)` 指的是20字符。检索 `VARCHAR` 类型的字段数据时，会保留数据尾部的空格。 `VARCHAR` 类型的字段所占用的存储空间为字符串实际长度加1个字节。 > 示例 ``` mysql> CREATE TABLE test_char(c1 CHAR,c2 CHAR(5)); Query OK, 0 rows affected (0.01 sec) mysql> DESC test_char; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c1 | char(1) | YES | | NULL | | | c2 | char(5) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> INSERT INTO test_char(c1,c2) VALUES('a','ab'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test_char; +------+------+ | c1 | c2 | +------+------+ | a | ab | +------+------+ 1 row in set (0.00 sec) mysql> CREATE TABLE test_varchar(`name` VARCHAR(5)); Query OK, 0 rows affected (0.01 sec) mysql> DESC test_varchar; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | name | varchar(5) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> INSERT INTO test_varchar VALUES('hello'),('world'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test_varchar; +-------+ | name | +-------+ | hello | | world | +-------+ 2 rows in set (0.00 sec) ``` > 哪些情况使用 `CHAR` 或 `VARCHAR` 类型 | 特点 | 空间上 | 时间上 | 适用场景 --- | --- | --- | --- | --- CHAR(M) | 固定长度 | 浪费存储空间 | 效率高 | 存储不大，速度要求高 VARCHAR(M) | 可变长度 | 节省存储空间 | 效率低 | 非 `CHAR` 的情况 - 情况1：存储很短的信息。比如门牌号码101、201……这样很短的信息应该用 `char` ，因为 `varchar` 还要占个 `byte` 用于存储信息长度。 - 情况2：固定长度的。比如使用 `uuid` 作为主键，用 `char` 更合适。因为固定长度， `varchar` 动态根据长度的特性就消失了，而且还要占个长度信息。 - 情况3：十分频繁改变的 `column` 。因为 `varchar` 每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于 `char` 来说是不需要的。 - 情况4：具体存储引擎中的情况： - `MyISAM` 数据存储引擎和数据列： `MyISAM` 数据表，最好使用固定长度 `(CHAR)` 的数据列代替可变长度 `(VARCHAR)` 的数据列。这样使得整个表静态化，从而使数据检索更快，用空间换时间。 - `MEMORY` 存储引擎和数据列： `MEMORY` 数据表目前都使用固定长度的数据行存储，因此无论使用 `CHAR` 或 `VARCHAR` 列都没有关系，两者都是作为 `CHAR` 类型处理的。 - `InnoDB` 存储引擎，建议使用 `VARCHAR` 类型。因为对于 `InnoDB` 数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于 `char` 平均占用的空间多于 `varchar` ，所以除了简短并且固定长度的，其他考虑 `varchar` 。这样节省空间，对磁盘 `I/O` 和数据存储总量比较好。 ## TEXT 长文本 在向 `TEXT` 类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和 `VARCHAR` 类型相同。每种 `TEXT` 类型保存的数据长度和所占用的存储空间不同，如下： 文本字符串类型 | 特点 | 长度 | 长度范围 | 占用的存储空间 --- | --- | --- | --- | --- TINYTEXT | 小文本、可变长度 | L | 0 <= L <= 255 | L + 2 个字节 TEXT | 文本、可变长度 | L | 0 <= L <= 65535 | L + 2 个字节 MEDIUMTEXT | 中等文本、可变长度 | L | 0 <= L <= 16777215 | L + 3 个字节 LONGTEXT | 大文本、可变长度 | L | 0 <= L<= 4294967295<br>（相当于4GB） | L + 4 个字节 由于实际存储的长度不确定，不允许 `TEXT` 类型的字段做主键。遇到这种情况，只能采用 `CHAR(M)` ，或者 `VARCHAR(M)` 。 > 示例 ``` mysql> CREATE TABLE test_text(tx TEXT); Query OK, 0 rows affected (0.01 sec) mysql> DESC test_text; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | tx | text | YES | | NULL | | +-------+------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> INSERT INTO test_text VALUES('helloworld'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test_text; +------------+ | tx | +------------+ | helloworld | +------------+ 1 row in set (0.00 sec) ``` ## ENUM 枚举 `ENUM` 类型也叫作枚举类型， `ENUM` 类型的取值范围需要在定义字段时进行指定。设置字段值时， `ENUM` 类型只允许从成员中选取单个值，不能一次选取多个值。其所需要的存储空间由定义 `ENUM` 类型时指定的成员个数决定。 类型 | 长度 | 长度范围 | 占用的存储空间 --- | --- | --- | --- ENUM | L | 1 <= L <= 65535 | 1或2个字节 - 当 `ENUM` 类型包含1～255个成员时，需要1个字节的存储空间； - 当 `ENUM` 类型包含256～65535个成员时，需要2个字节的存储空间。 - `ENUM` 类型的成员个数的上限为65535个。 > 示例 ``` mysql> CREATE TABLE test_enum(season ENUM('春','夏','秋','冬','unknow')); Query OK, 0 rows affected (0.02 sec) mysql> DESC test_enum; +--------+----------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------------------------+------+-----+---------+-------+ | season | enum('春','夏','秋','冬','unknow') | YES | | NULL | | +--------+----------------------------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> INSERT INTO test_enum VALUES('春'),('秋'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test_enum; +--------+ | season | +--------+ | 春 | | 秋 | +--------+ 2 rows in set (0.00 sec) ``` ## SET 集合 `SET` 表示一个字符串对象，可以包含0个或多个成员，但成员个数的上限为64。设置字段值时，可以取取值范围内的0个或多个值。当 `SET` 类型包含的成员个数不同时，其所占用的存储空间也是不同的，具体如下： 成员个数范围（L表示实际成员个数） | 占用的存储空间 --- | --- 1 <= L <= 8 | 1个字节 9 <= L <= 16 | 2个字节 17 <= L <= 24 | 3个字节 25 <= L <= 32 | 4个字节 33 <= L <= 64 | 8个字节 `SET` 类型在存储数据时成员个数越多，其占用的存储空间越大。注意：`SET` 类型在选取成员时，可以一次选择多个成员，这一点与 `ENUM` 类型不同。 > 示例 ``` mysql> CREATE TABLE test_set(s SET ('A', 'B', 'C')); Query OK, 0 rows affected (0.00 sec) mysql> DESC test_set; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | s | set('A','B','C') | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> INSERT INTO test_set (s) VALUES ('A'), ('A,B'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test_set; +------+ | s | +------+ | A | | A,B | +------+ 2 rows in set (0.00 sec) ``` # 其他 - `BIT` 位类型：中存储的是二进制值 - `BINARY` 与 `VARBINARY` 主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据 - `BLOB` 类型是一个**二进制大对象**，可以容纳可变数量的数据。包括 `TINYBLOB` 、 `BLOB` 、 `MEDIUMBLOB` 和 `LONGBLOB` 4种类型 - `JSON` 是一种轻量级的**数据交换格式**。 - 空间类型 - 单值类型：GEOMETRY、POINT、LINESTRING、POLYGON - 集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION 。 # 选择建议 在定义数据类型时 - 如果确定是**整数**，就用 `INT` - 如果是**小数**，一定用定点数类型 `DECIMAL(M,D)` - 如果是日期与时间，就用 `DATETIME` 关于字符串的选择，建议参考如下阿里巴巴的《Java开发手册》规范：阿里巴巴《Java开发手册》之MySQL数据库： - 任何字段如果为非负数，必须是 `UNSIGNED` - 【 强制 】小数类型为 `DECIMAL` ，禁止使用 `FLOAT` 和 `DOUBLE` 。 - 说明：在存储的时候，`FLOAT` 和 `DOUBLE` 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 `DECIMAL` 的范围，建议将数据拆成整数和小数并分开存储。 - 【 强制 】如果存储的字符串长度几乎相等，使用 `CHAR` 定长字符串类型。 - 【 强制 】 `VARCHAR` 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为 `TEXT` ，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
3