MySQL 5.7（二）【数据类型与运算符】

一、数据类型

MySQL支持多种数据类型，主要有 数值类型、日期/时间类型 和 字符串类型。

1. 数值数据类型：包括整数类型 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型 FLOAT 和 DOUBLE，定点小数类型 DECIMAL。
2. 日期/时间类型：包括YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。
3. 字符串类型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。字符串类型又分为文本字符串和二进制字符串。

（一）整数类型

注意：显示宽度和数据类型的取值范围无关。显示宽度只是指明MySQL最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；如果插入了大于显尿宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。

整数类型的默认显示宽度与其有符号数的最小值的宽度相同（例：TINYINT(4)，-128）。
 

（二）浮点数类型和定点数类型

不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。

 CREATE TABLE tmp2(x FLOAT(5,1),y DOUBLE(5,1),z DECIMAL(5,1));

注意：FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度(由计算机硬件和操作系统决定)，DECIMAL如不指定精度，默认为(10,0)。浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。
在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候(如货币，科学数据等)使用DECIMAL的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点型时需要注意，并尽量避免做浮点数比较。
 

（三）日期与时间类型

每一个类型都有合法的取值范围，当指定确实不合法的值时系统将“零”值插入到数据库中。

1、YEAR

（1）4位字符串或4位数字格式表示 YEAR

1. 范围：‘1901’ ~ ‘2155’
2. 输入格式：‘YYYY’ 或 YYYY，插入到数据库的值均为 2010

 INSERT INTO TEMP_TB_01 VALUES(2010),('2010');

运行结果：

（2）2位字符串格式表示 YEAR

1. 范围：‘00’ ~ ‘99’
2. 格式：‘00’ ~ ‘69’ 和 ‘70’ ~ ‘99’ 分别被转换为 2000 ~ 2069 和 1970 ~ 1999 ，‘0’ 与 ‘00’ 作用相同。插入超过取值范围的值将被转换为 2000 。

 INSERT INTO TEMP_TB_01 VALUES('0'),('00'),('77'),('10');

运行结果：

（3）2位数字表示 YEAR

1. 范围：1 ~ 99
2. 格式：1 ~ 69 和 70 ~ 99 分别被转换为 2001 ~ 2069 和 1970 ~ 1999 ，0 值被转换为0000。

 INSERT INTO TEMP_TB_01 VALUES(0),(78),(11);

运行结果：

2、TIME

（1）‘D HH:MM:SS’ 格式的字符串

1. 范围：D 表示日（day），0 ~ 34 ；总的范围： -838:59:59 ~ 838:59:59
2. 格式：‘D HH:MM:SS’ 、‘HH:MM:SS’ 、‘HH:MM’ 、‘D HH:MM’ 、‘D HH’ 、‘SS’。在插入数据库时，D 被转换为小时（D*24+HH）。在使用‘D HH’格式时,小时一定要使用双位数值，如果是小于10的小时数,应在前面加0。

 INSERT INTO TEMP_TB_02 VALUES('1 09:25:32'),('10:05:05'),('23:23'),('2 10:15'),('2 06'),('15');

运行结果：

（2）‘HHMMSS’ 格式的、没有间隔符的字符串或 HHMMSS 格式的数值（有意义的时间）

1. 范围： -838:59:59 ~ 838:59:59
2. 格式：‘101152’ 或 101532 ，没有意义的将被转换为00:00:00。

 INSERT INTO TEMP_TB_02 VALUES('151112'),(203245),('0'),(0);

运行结果：

（3）使用函数插入系统时间

 INSERT INTO TEMP_TB_02 VALUES(CURRENT_TIME),(NOW());

运行结果：

3、DATE

（1）以 ‘YYYY-MM-DD’ 或者 'YYYYMMDD’ 字符串格式表示日期

1. 范围：‘1000-01-01’ ~ ‘9999-12-3’
2. 格式：‘2012-12-31’ 或 ‘20121231’ ，插入数据库的日期为2012-12-31

 INSERT INTO TEMP_TB_03 VALUES('1998-08-08'),('19980808'),('20101010');

（2）以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期

1. 范围：YY表示两位的年值，‘00 ~ 69’ 转换为‘2000 ~ 2069’ ；‘70 ~ 99’ 转换为“1970 ~ 1999’
2. 格式：‘12-12-31’ ，插入数据库的日期为2012-12-31；‘981231’ ，插入数据的日期为1998-12-31。

 INSERT INTO TEMP_TB_03 VALUES('99-09-09'),('990909'),('000101'),('111111');

（3）以 YYMMDD 数字格式表示的日期

1. 范围：00 ~ 69 范围的年值转换为2000 ~ 2069；70 ~ 99范围的年值转换为1970 ~ 1999
2. 格式：981231插入数据的日期为1998-12-31。

 INSERT INTO TEMP_TB_03 VALUES(990909),(000101),(111111);

（4）使用 CURRENT_ DATE 或 NOW()，插入当前系统日期

 INSERT INTO TEMP_TB_03 VALUES(CURRENT_DATE),(NOW());

4、DATETIME

（1）以 ‘YYYY-MM-DD HH:MM:SS’ 或 ‘YYYYMMDDHHMMSS’ 字符串格式表示
的值

1. 范围：‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’
2. 格式输入 ‘ 2012-12-31 05:05:05’ 或 ‘20121231050505’ ，插入数据库的值都为2012-12-31 05: 05: 05

 INSERT INTO TEMP_TB_04 VALUES('1998-09-12 05:11:53'),('19980912051153'),('20101010101010');

（2）以 ‘YY-MM-DD HH:MM:SS’ 或 ‘YYMMDDHHMMSS’ 字符串格式表示的日期

1. 范围：YY表示两位的年值。与前面相同
2. 格式输入：‘12-12-31 05: 05: 05’，插入数据库的值为2012-12-31 05: 05: 05；输入 ‘980505050505’ ，插入数据库的值为 1998-05-05 05: 05: 05

 INSERT INTO TEMP_TB_04 VALUES('98-09-12 05:11:53'),('980912051153'),('101010101010');

（3）以 YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 数字格式表示的日期和时间

1. 格式输入：20121231050505， 插入数据库的值为2012-12-31 05:05:05；输入
   981231050505，插入数据的值为1998-12-31 05: 05: 05

 INSERT INTO TEMP_TB_04 VALUES(19980912051153),(101010101010);

（4）使用 NOW()，插入当前系统日期

 INSERT INTO TEMP_TB_04 VALUES(NOW());

5、TIMESTAMP

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在19个字符，日期格式为 YYYY-MM-DD HH:MM:SS，在存储时需要4个字节。但是 TIMESTAMP 列的取值范围小于DATETIME的取值范围，为 ‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC，其中，UTC (Coordinated Universal Time)，为世界标准时间，因此在插入数据时，要保证在合法的取值范围内。

 INSERT INTO TEMP_TB_05 VALUES('19950101010101'),
 ('950505050505'),
 ('1996-02-02 02:02:02'),
 ('97@03@03 03@03@03'),
 (121212121212),
 (NOW());

运行结果：

更改时区

 SET TIME_ZONE='+10:00';

前后对比：如图：

（四）文本字符串类型

1、CHAR 和 VARCHAR类型

2、TEXT类型

保存或查询TEXT 列的值时，不删除尾部空格。

分四种类型
（1）TINYTEXT 最大长度为255字符的 TEXT 列；
（2）TEXT 最大长度为65535字符的 TEXT 列；
（3）MEDIUMTEXT 最大长度为 16777215字符的 TEXT 列；
（4）LONGTEXT 最大长度为4294967295或4GB字符的 TEXT 列。

3、ENUM类型

4、SET类型

（五）二进制字符串类型

二、如何选择数据类型

（一）整数和浮点数

常规用法即可

（二）浮点数和定点数

浮点数（float，double）：在长度一定时，其能表示更大的数据范围，但容易产生误差，迁移能力差，数值比较能力差。
定点数（decimal）：精确度较高，数值比较能力较强。

（三）日期与时间

（1）YEAR 与 TIME 常规用法即可。
（2）同时记录日期和时间，存储范围较大的日期时使用DATETIME；当既要插入记录同时插入当前时间时，使用TIMESTAMP。

（四）CHAR 与 VARCHAR

二者区别：

• char 固定长度字符串，自动删除插入数据的尾部空格，处理速度快，但浪费存储空间
• varchar 可变长字符串，不会删除尾部空格，处理速度慢，存储空间利用率高。
  存储引擎对二者的影响：
• MyISAM 存储引擎：最好使用固定长度数据列代替可变长数据列，以使整个表静态化，从而使数据检索更快，用空间换时间。
• InnoDB 存储引擎：使用可变长数据列（InnoDB数据表不分固定与可变长），varchar 按实际长度存储，节省空间，对磁盘I/O和数据存储总量比较好。

（五）ENUM 和 SET

ENUM 只能取单值，在需要从多个值中选取一个时，使用 ENUM（例：性别字段）。SET 可取多值，在需要取多值时使用（例：兴趣字段）。

（六）BLOB 与 TEXT

BLOB 是二进制字符串，主要存储图片，音频信息等；TEXT是非二进制字符串，只能存储纯文本文件。二者皆可存放大容量信息。

三、常见运算符比较

常见的不在赘述

比较运算符

REGEXP 解析：

位运算符

运算符优先级

注意：
在Windows 平台，MySQL 不区分大小写，除非在字符串前使用 BINARY 关键字。