数据库系统原理与应用教程(022)—— MySQL 总结支持的数据类型
在创建数据表时，需要为表中的列指定适当的数据类型。MySQL支持多种类型，大致可分为数值、日期/时间和字符串三类。
一、数值型数据
数值类型分为整型、定点数类型和浮点数类型。
1、整型
整型包括 TINYINT、SMALLINT、MEDIUMINT、 INT、 BIGINT 等类型。具体用法如下表所示：
| MySQL数据类型 | 含义 |
|---|---|
| tinyint(m) | 长度 1 个字节，~127 |
| smallint(m) | 长度 2 -327688~32767 |
| mediumint(m) | 长度 3 取值范围为-8388608~8388607 |
| int(m) | 长度 4 取值范围为-2147483648~2147483647 |
| bigint(m) | 长度 8 个字节，取值范围:-9.22×1018—9.22×1018 |
说明：
（1）可以使用 unsigned 修改符号定义无符号整形，如 tinyint unsigned 取值范围为(0~256)。
（2）int(m) 中的 m 查询结果中的显示宽度不影响实际值范围。
例如：
mysql> create table t12(id int(15) primary key,name char(20)); Query OK, 0 rows affected (0.25 sec) mysql> insert into t12 values(1001,'Jack'),(200101,'Mark'),(302321411,'Tom'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t12; ----------- ------ | id | name |
+-----------+------+
| 1001 | Jack |
| 200101 | Mark |
| 302321411 | Tom |
+-----------+------+
3 rows in set (0.00 sec)
-- 定义 int(5) 并不影响 int 的取值范围。
mysql> create table t11(id int(5) primary key,name char(20));
Query OK, 0 rows affected (0.24 sec)
mysql> insert into t11 values(1001,'Jack'),(200101,'Mark'),(302321411,'Tom');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t11;
+-----------+------+
| id | name |
+-----------+------+
| 1001 | Jack |
| 200101 | Mark |
| 302321411 | Tom |
+-----------+------+
3 rows in set (0.00 sec)
2、浮点数类型
浮点数类型包括 float 和 double 两类，具体使用方法如下表所示：
| MySQL数据类型 | 含义 |
|---|---|
| float(m,d) | 单精度浮点型，8位精度（4字节），m表示总长度，d表示小数位数 |
| double(m,d) | 双精度浮点型，16位精度（8字节），m表示总长度，d表示小数位数 |
说明：如果把某个字段定义为 float(7,2)，表示该字段保留 2 位小数，总长度为 7 位，即整数最大为 5 位。
（1）如果插入一个数 123.45678，实际数据库里存的是 123.46；
（2）如果插入一个数 12.123456，存储的是 12.12，如果插入 1200，存储的是 1200.00。
（3）如果整数部分超过 5 位，则发生错误。
例如：
mysql> create table t21(id int primary key, name char(20), salary float(7,2));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t21 values(1,'张鹏',5800);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t21;
+----+--------+---------+
| id | name | salary |
+----+--------+---------+
| 1 | 张鹏 | 5800.00 | -- 小数部分自动补 0
+----+--------+---------+
1 row in set (0.00 sec)
mysql> insert into t21 values(2,'李军',5628.258);
Query OK, 1 row affected (0.04 sec)
mysql> select * from t21;
+----+--------+---------+
| id | name | salary |
+----+--------+---------+
| 1 | 张鹏 | 5800.00 |
| 2 | 李军 | 5628.26 |
+----+--------+---------+
2 rows in set (0.00 sec)
mysql> insert into t21 values(3,'张静',105628.258); -- 错误（整数 6 位）
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
3、定点数类型
浮点数类型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。 定点数类型为 decimal(m,d)，m 和 d 的含义与浮点数类型完全相同。其中参数 m<65，d<30。
例如：
mysql> create table t22(id int primary key, name char(20), salary decimal(7,2));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t22 values(1,'张鹏',5800);
Query OK, 1 row affected (0.24 sec)
mysql> insert into t22 values(2,'李军',5628.258);
Query OK, 1 row affected, 1 warning (0.04 sec)
mysql> insert into t22 values(3,'张静',105628.258);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> select * from t22;
+----+--------+---------+
| id | name | salary |
+----+--------+---------+
| 1 | 张鹏 | 5800.00 |
| 2 | 李军 | 5628.26 |
+----+--------+---------+
2 rows in set (0.00 sec)
二、字符类型
字符串类型包括：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 、SET 等类型。
1、CHAR 和 VARCHAR 类型
说明：
（1）语法格式为： CHAR(n)， VARCHAR(n)；
（2）CHAR 和 VARCHAR 类型需要指定长度，长度表示要保存的最大字符数。例如： CHAR(20) 表示最多可以保存 20 个字符。
（3）CHAR 类型的长度可以为 0 到255之间的任何值。当保存 CHAR 类型的值时，如果字符数少于指定的长度，则在右边填充空格以达到指定的长度。当检索 CHAR 类型的值时，尾部的空格被删除。所以，我们在存储 char 类型的值时，字符串结尾不能有空格，即使有，查询出来后也会被删除。
（4）VARCHAR 列中的值为可变长字符串，长度可以指定为 0 到 65535 之间的值。与 CHAR 类型相比， VARCHAR 类型的值保存时只保存需要的字符数，另加一个字节来记录长度（如果列声明的长度超过255，则使用两个字节）。 VARCHAR 类型的值如果需要存储的字符个数少于指定的长度，不在尾部填充空格。检索时尾部的空格仍保留。
（5）如果需要保存的字符串的长度超过了 CHAR 或 VARCHAR 列指定的长度，会出现错误。
（6）char 类型的字符串检索速度要比 varchar 类型的快。
例如：
mysql> create table t41(id int primary key, name char(3), gender char(1));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t41 values(1,'Tom','M');
Query OK, 1 row affected (0.04 sec)
mysql> select * from t41;
+----+------+--------+
| id | name | gender |
+----+------+--------+
| 1 | Tom | M |
+----+------+--------+
1 row in set (0.00 sec)
mysql> insert into t41 values(1,'Jack','M'); -- 超过 3 个字符
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into t41 values(2,'张静静','女');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t41;
+----+-----------+--------+
| id | name | gender |
+----+-----------+--------+
| 1 | Tom | M |
| 2 | 张静静 | 女 |
+----+-----------+--------+
2 rows in set (0.00 sec)
mysql> insert into t41 values(3,'诸葛孔明','男'); -- 超过 3 个字符
ERROR 1406 (22001): Data too long for column 'name' at row 1
测试 char 和 varchar 是否自动添加空格：
mysql> create table t42(id int primary key, name char(10), dept_name varchar(200));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into t42 values(1, '张静静 ', '财务部');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t42 values(2, '刘涛 ', '财务部 ');
Query OK, 1 row affected (0.04 sec)
mysql> select id, concat('Name:',name,'11') name,
concat('dept_name:',dept_name,'11')
from t42;
-- char 类型插入数据时会自动在结尾添加空格，但显示时会删除尾部空格
-- varchar 类型插入数据时不会自动在结尾添加空格，显示时也不删除空格
+----+------------------+-------------------------------------+
| id | name | concat('dept_name:',dept_name,'11') |
+----+------------------+-------------------------------------+
| 1 | Name:张静静11 | dept_name:财务部11 |
| 2 | Name:刘涛11 | dept_name:财务部 11 |
+----+------------------+-------------------------------------+
2 rows in set (0.00 sec)
2、BINARY 和 VARBINARY 类型
BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR 类型。不同的是，它们存储的是二进制串，因此没有字符集。当保存 BINARY 数据值时，在尾部填充 0x00（零）值以达到指定长度，取值时不删除尾部的字节。对于 VARBINARY，插入时不填充字符，选择时不裁剪字节。
BINARY 和 VARBINARY 类型的长度是字节数，而不是字符数。一个西文字符占用一个字节，汉字占用的字节数和字符集有关。可以使用 show character set 命令查询各字符集中汉字所用的字节数：
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.12 sec)
-- 字符集 utf8 每个汉字占用 3 个字节。
例如：
mysql> create table t51(id int primary key,name binary(12),addr varbinary(20));
Query OK, 0 rows affected (0.89 sec)
mysql> show create table t51\G
*************************** 1. row ***************************
Table: t51
Create Table: CREATE TABLE `t51` (
`id` int(11) NOT NULL,
`name` binary(12) DEFAULT NULL,
`addr` varbinary(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
-- 表 t51 使用的字符集为 utf8，则一个汉字占用 3 个字节。name 列最多保存 4 个汉字，addr 列最多保存 6 个汉字。
mysql> insert into t51 values(1, '张静静', '财务部');
Query OK, 1 row affected (0.05 sec)
mysql> select * from t51;
+----+--------------+-----------+
| id | name | addr |
+----+--------------+-----------+
| 1 | 张静静 | 财务部 |
+----+--------------+-----------+
1 row in set (0.03 sec)
mysql> insert into t51 values(2, '张静静2', '财务部');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t51 values(3, '刘涛 ', '财务部 ');
Query OK, 1 row affected (0.01 sec)
-- 插入失败，name 列超过了指定长度
mysql> insert into t51 values(4, '诸葛孔明1', '财务部 ');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select * from t51;
+----+--------------+-------------+
| id | name | addr |
+----+--------------+-------------+
| 1 | 张静静 | 财务部 |
| 2 | 张静静2 | 财务部 |
| 3 | 刘涛 | 财务部 |
+----+--------------+-------------+
<