Article Outline
深入浅出MySQL阅读笔记二:数据类型
1. 数值类型
MySQL 支持所有标准 SQL 中的数值类型，其中包括严格数值类型（INTEGER、SMALLINT、 DECIMAL 和 NUMERIC），以及近似数值数据类型（FLOAT、REAL 和 DOUBLE PRECISION），并 在此基础上做了扩展。扩展后增加了 TINYINT、MEDIUMINT 和 BIGINT 这 3 种长度不同的整 型，并增加了 BIT 类型，用来存放位数据。表 3-1 中列出了 MySQL 5.0 中支持的所有数值类 型，其中 INT 是 INTEGER 的同名词，DEC 是 DECIMAL 的同名词。
| 整数类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| TINYINT | 1 | 有符号-128,无符号0 | 有符号127,无符号255 |
| SMALLINT | 2 | 有符号-32768,无符号0 | 有符号32767,无符号65535 |
| MEDIUMINT | 3 | 有符号-8388608,无符号0 | 有符号8388607,无符号1677215 |
| INT、INTEGER | 4 | 有符号-2147483648,无符号0 | 有符号2147483647,无符号4294967295 |
| BIGINT | 8 | 有符号-9223372036854775808,无符号0 | 有符号9223372036854775807,无符号18446744073709551615 |
| 浮点数类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| FLOAT | 4 | ±1.175494351E-38 | ±3.402823466E+38 |
| DOUBLE | 8 | ±2.2250738585072014E-308 | ±1.7976931348623157E+308 |
| 定点数类型 | 字节 | 描述 |
|---|---|---|
| DEC(M,D),DECIMAL(M,D) | M+2 | 最大取值范围与 DOUBLE 相同,给定 DECIMAL 的有效取值范围由 M 和 D决定 |
| 位类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| BIT(M) | 1～8 | BIT(1) | BIT(64) |
1.1 整数
对于整型数据，MySQL 还支持在类型名称后面的小括号内指定显示宽度，例如 int(5)表 示当数值宽度小于 5 位的时候在数字前面填满宽度，如果不显示指定宽度则默认为 int(11)。 一般配合 zerofill 使用，顾名思义，zerofill 就是用“0”填充的意思，也就是在数字位数不够 的空间用字符“0”填满,也就是左边补0
-- 建立测试表
create table t1 (
id1 int zerofill,
id2 int(5) zerofill);
-- 插入数据
insert into t1 values(1,1),(111111,111111);结果:
mysql> select * from t1;
+------------+--------+
| id1 | id2 |
+------------+--------+
| 0000000001 | 00001 |
| 0000111111 | 111111 |
+------------+--------+结论:
- int 默认int(11)
- 插入大于宽度限制的值,不会截取;int(5)可以插入111111
- 另外,指定为zerofill,则 MySQL 自动为该列添加
UNSIGNED属性
另外，整数类型还有一个属性：AUTO_INCREMENT。在需要产生唯一标识符或顺序值时， 可利用此属性，这个属性只用于整数类型。AUTO_INCREMENT 值一般从 1 开始，每行增加 1。
一个表中最多只能有一个AUTO_INCREMENT列。
对于任何想要使用AUTO_INCREMENT 的 列，应该定义为 NOT NULL，并定义为 PRIMARY KEY 或定义为 UNIQUE 键
1.2 小数
对于小数的表示，MySQL 分为两种方式：浮点数和定点数。浮点数包括 float（单精度）
和 double（双精度），而定点数则只有 decimal 一种表示。定点数在 MySQL 内部以字符串形
式存放，比浮点数更精确，适合用来表示货币等精度高的数据。
浮点数和定点数都可以用类型名称后加“(M,D)”的方式来进行表示,(整数位+小数位位数,小数位位数) 整数位是M-D,小数位是D
MySQL 保存值时进行四舍五入，因此如果在 float(7,4)列内插入 999.00009，近似结果是 999.0001
值得注意的是，浮点数后面跟“(M,D)”的用法是非标准用法，如果要用于数据库的迁移，则最好不要这么使用。
float 和 double 在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定） 来显示，而 decimal 在不指定精度时，默认(10,0)。
浮点数的保存,小数位基本上一定损失精度,除非是0.5,0.25,0.5+0.25这种,所以如果涉及计算,尽量避免使用浮点数,使用decimal e.g.
-- 建表
drop table if exists t1;
create table t1(
id float(8,5)
);
-- 插入数据
insert into t1 values(999.00999);结果:
mysql> select * from t2;
+------------+
| id |
+------------+
| 999.010010 |
+------------+
1 row in set (0.00 sec)看起来很奇怪的...
1.3 位类型(bit)
-- 建表
drop table if exists t2;
create table t2(
id bit(4)
);
-- 插入数据
insert into t2 values(2);结果:
mysql> select * from t2;
+------+
| id |
+------+
| |
+------+
1 row in set (0.00 sec)不会显示数值,需要用bin(),或者hex()[十六进制]取出
mysql> select bin(id),hex(id) from t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 10 | 2 |
+---------+---------+
1 row in set (0.00 sec)2. 日起时间类型
MySQL 5.0 中所支持的日期和时间类型
| 日期和时间类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| DATE | 4 | 1000-01-01 | 9999-12-31 |
| DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
| TIMESTAMP | 4 | 19700101080001 | 2038 年的某个时刻 |
| TIME | 3 | -838:59:59 | 838:59:59 |
| YEAR | 1 | 1901 | 2155 |
如果要用来表示年月日时分秒，通常用 DATETIME 表示。
如果需要经常插入或者更新日期为当前系统时间，则通常使用 TIMESTAMP 来表示。 TIMESTAMP 值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串，显示宽度固定 为 19 个字符。如果想要获得数字值，应在 TIMESTAMP 列添加+0。
每种日期时间类型都有一个有效值范围，如果超出这个范围，在 默认的 SQLMode 下，系统会进行错误提示，并将以零值来进行存储。
| 数据类型 | 零值表示 |
|---|---|
| DATETIME | 0000-00-00 00:00:00 |
| DATE | 0000-00-00 |
| TIMESTAMP | 00000000000000 |
| TIME | 00:00:00 |
| YEAR | 0000 |
MySQL会给表中的第一个TIMESTAMP字段设置默认值为系统日期，如果有第二个TIMESTAMP类型，则默认值设置为0值
-- 建表
drop table if exists t2;
create table t2(
ts1 timestamp,
ts2 timestamp
);
--- 实际的sql语句
CREATE TABLE `t2` (
`ts1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ts2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;可见,timestamp类型的字段在记录修改的时候也会默认的更新为当前系统时间
TIMESTAMP还有一个重要特点，就是**和时区相关。当插入日期时，会先转换为本地时区 后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。
-- 查看当前时区
show variables like 'time_zone';结果:
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)默认系统时区,中国东八时区(+8:00)
-- 建表
drop table if exists t2;
CREATE TABLE `t2` (
`id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
`id2` datetime default NULL
);
-- 插入数据
insert into t2 values(now(),now());
-- 修改时区
set time_zone='+9:00';结果:
mysql> select * from t2;
+---------------------+---------------------+
| id1 | id2 |
+---------------------+---------------------+
| 2018-11-07 20:05:02 | 2018-11-07 19:05:02 |
+---------------------+---------------------+注意:set time_zone = '+9:00' 这种设置时区的方式只对当前session有效
从上面例子可以看出，TIMESTAMP和DATETIME的表示方法非常类似，区别主要有以下 几点。
- TIMESTAMP支持的时间范围较小，其取值范围从19700101080001到2038年的某个 时间，而DATETIME是从1000-01-01 00:00:00到9999-12-31 23:59:59，范围更大。
- 表中的第一个TIMESTAMP列自动设置为系统时间。如果在一个TIMESTAMP列中插入 NULL，则该列值将自动设置为当前的日期和时间。在插入或更新一行但不明确给 TIMESTAMP列赋值时也会自动设置该列的值为当前的日期和时间，当插入的值超出 取值范围时，MySQL认为该值溢出，使用“0000-00-00 00:00:00”进行填补。
- TIMESTAMP的插入和查询都受当地时区的影响，更能反应出实际的日期。而 DATETIME则只能反应出插入时当地的时区，其他时区的人查看数据必然会有误差 的。
- TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大，本章都是以MySQL
5.0为例进行介绍，在不同的版本下可以参考相应的MySQL帮助文档
3. 字符串类型
5.0版本
| 字符串类型 | 字节 | 描述及存储需求 |
|---|---|---|
| CHAR（M） | M | M 为 0～255 之间的整数 |
| VARCHAR（M） | M | 为 0～65535 之间的整数，值的长度+1 个字节 |
| TINYBLOB | 允许长度 0～255 字节，值的长度+1 个字节 | |
| BLOB | 允许长度 0～65535 字节，值的长度+2 个字节 | |
| MEDIUMBLOB | 允许长度 0～167772150 字节，值的长度+3 个字节 | |
| LONGBLOB | 允许长度 0～4294967295 字节，值的长度+4 个字节 | |
| TINYTEXT | 允许长度 0～255 字节，值的长度+2 个字节 | |
| TEXT | 允许长度 0～65535 字节，值的长度+2 个字节 | |
| MEDIUMTEXT | 允许长度 0～167772150 字节，值的长度+3 个字节 | |
| LONGTEXT | 允许长度 0～4294967295 字节，值的长度+4 个字节 | |
| VARBINARY（M） | 允许长度 0～M 个字节的变长字节字符串，值的长度+1 个字节 | |
| BINARY（M） | M | 允许长度 0～M 个字节的定长字节字符串 |
3.1 char和varchar
- 存储方式上:char定长,varchar可变长
- char会删除字符串尾部的空格,varchar不会
3.2 enum类型
create table t (gender enum('M','F'));插入的时候,插入其他值,默认插入枚举项的第一个
3.3 set类型
Create table t (col set （'a','b','c','d'）;
-- set和enum区别是set可以插入多个值
insert into t values('a,b'),('a,d,a'),('a,b'),('a,c'),('a');不在范围内的值,不允许插入
去重,('a','b','a')-->('a','b')
小结
本章主要介绍了 MySQL 支持的各种数据类型，并通过多个实例对它们的使用方法做了详细 的说明。学完本章后，读者可以对每种数据类型的用途、物理存储、表示范围等有一个概要 的了解。这样在面对具体应用时，就可以根据相应的特点来选择合适的数据类型，使得我们 能够争取在满足应用的基础上，用较小的存储代价换来较高的数据库性能。