第二篇
一、数据类型
MySQL支持多种数据类型,主要有 数值类型、日期/时间类型 和 字符串类型。
- 数值数据类型:包括整数类型 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型 FLOAT 和 DOUBLE,定点小数类型 DECIMAL。
- 日期/时间类型:包括YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。
- 字符串类型:包括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
- 范围:‘1901’ ~ ‘2155’
- 输入格式:‘YYYY’ 或 YYYY,插入到数据库的值均为 2010
INSERT INTO TEMP_TB_01 VALUES(2010),('2010');
运行结果:
(2)2位字符串格式表示 YEAR
- 范围:‘00’ ~ ‘99’
- 格式:‘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 ~ 99
- 格式: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’ 格式的字符串
- 范围:D 表示日(day),0 ~ 34 ;总的范围: -838:59:59 ~ 838:59:59
- 格式:‘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 格式的数值(有意义的时间)
- 范围: -838:59:59 ~ 838:59:59
- 格式:‘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’ 字符串格式表示日期
- 范围:‘1000-01-01’ ~ ‘9999-12-3’
- 格式:‘2012-12-31’ 或 ‘20121231’ ,插入数据库的日期为2012-12-31
INSERT INTO TEMP_TB_03 VALUES('1998-08-08'),('19980808'),('20101010');
(2)以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期
- 范围:YY表示两位的年值,‘00 ~ 69’ 转换为‘2000 ~ 2069’ ;‘70 ~ 99’ 转换为“1970 ~ 1999’
- 格式:‘12-12-31’ ,插入数据库的日期为2012-12-31;‘981231’ ,插入数据的日期为1998-12-31。
INSERT INTO TEMP_TB_03 VALUES('99-09-09'),('990909'),('000101'),('111111');
(3)以 YYMMDD 数字格式表示的日期
- 范围:00 ~ 69 范围的年值转换为2000 ~ 2069;70 ~ 99范围的年值转换为1970 ~ 1999
- 格式: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’ 字符串格式表示
的值
- 范围:‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’
- 格式输入 ‘ 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’ 字符串格式表示的日期
- 范围:YY表示两位的年值。与前面相同
- 格式输入:‘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 数字格式表示的日期和时间
- 格式输入: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 关键字。