MySQL数据类型

支持的数据类型

MySQL查看支持的数据类型：? data types;
常用的数据类型：

数值型

类型	大小	范围(有符号)	范围(无符号)	用途
TINYINT	1字节	-128 ~ 127	0 ~ 255	小整数值
SMALLINT	2字节	-215 ~ 215-1	0 ~ 216-1	大整数值
MEDIUMINT	3字节	-223 ~ 223-1	0 ~ 224-1	大整数值
INT	4字节	-231 ~ 231-1	0 ~ 232-1	大整数值
BIGINT	8字节	-263 ~ 263-1	0 ~ 264-1	极大整数值
FLOAT	4字节	暂定	暂定	单精度浮点值
DOUBLE	8字节	暂定	暂定	双精度浮点值
DECIMAL(m,d)	m字节（m<65,d<30）	暂定	暂定	精确数据

• 数值默认为有符号类型，如需定义无符号类型，在类型后面加上UNSIGNED即可
• 数值类型也可传入长度信息，如TINYINT(10)，不过这里是显示长度，并且字段设置ZEROFILL时才会在前面补0
• 部分数据库可视化软件不支持前导0

• FLOAT、DOUBLE也可定义长度与有效数字，如FLOAT(10,2)定义为10位总数，2位有效数字，设值的时候，整数位与小数位加起来不能超过10位
• FLOAT与DOUBLE都有最大值，如FLOAT最大值为2²³=8388608，超过该值，会有精度丢失（小数位四舍五入）
• 需要精确数值类型的，一定不要使用FLOAT或者DOUBLE，要使用DECIMAL

日期时间类型

类型	大小	范围	格式	用途
DATE	3字节	'1000-01-01' ~ '9999-12-31'	YYYY-MM-DD	日期值
TIME	3字节	'-838:59:59' ~ '838:59:59'	HH:MM:SS	时间值
YEAR	1字节	'1901' ~ '2155'	YYYY	年份值
DATETIME	8字节	'1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4字节	'1970-01-01 00:00:01' ~ '2038-01-19 03:14:07'	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

TIMESTAMP与DATETIME范围有区别，明显DATETIME范围更大
TIMESTAMP与DATETIME格式的字段在存入的时候直接存入YYYYMMDDHHMMSS格式的字符串
TIMESTAMP存储时将插入的时间转化为UTC时间进行存储，查询时又转换为当前时区的时间进行返回；DATETIME则插入什么就存储什么

时间类型涉及到的一个重要概念就是时区，使用命令show variables like '%time_zone%'查看当前系统时区设置：

system_time_zone 表示全局时区
time_zone 表示当前会话时区

通过set global time_zone = 或set time_zone = 可设置时区。

字符串类型

类型	大小	用途
CHAR	0 - 255字节	定长字符串
VARCHAR	0 - 216-1字节	变长字符串
TINYBLOB	0 - 255字节	二进制短字符串
TINYTEXT	0 - 255字节	短文本
BLOB	0 - 216-1字节	二进制长文本
TEXT	0 - 216-1字节	长文本
MEDIUMBLOB	0 - 224-1字节	二进制中等长度文本数据
MEDIUMTEXT	0 - 224-1字节	中等长度文本
LONGBLOB	0 - 232-1字节	二进制极大长度文本数据
LONGTEXT	0 - 232-1字节	极大长度文本数据

CHAR的长度固定，不受存储的内容长度影响，如定义CHAR(30)，就算存一个字符，也占30字节大小
CHAR好处是处理速度快，缺点是占用空间大
VARCHAR的占用空间受内容的影响，但速度不如CHAR

字符集

使用命令show character set;可查看系统支持的字符集列表，其中utf8包括的文字内容较广，是比较常用的字符集
字符集设置一般分级别，下级无单独配置时，遵从上级的设置，级别如下：

• 服务器级别
• 数据库级别
• 表级别
• 字段级别

校对规则

使用命令show COLLATION;可查看系统支持的校对规则，列表中_ci后缀表达不区分大小写，_bin表示区分大小写
使用命令show create table table_name;可查看表新建时的字符集与校对规则配置

设置表级别的校对规则：

create table table_name(
id int primary key auto_increment,
name varchar(30))
charset = utf8 collate = utf8_bin;

上述表结构在新建时，指定了表级别的校对规则为utf8_bin（utf8字符集默认校对规则为utf8_general_ci），后续新增或修改的字段如果没有对collation进行定义，将会沿用表校对规则的设置
修改表的默认校对规则，可通过ALTER TABLE table_name DEFAULT COLLATE collate_name;进行设置，但不会对原字段的规则有所影响，只会影响后续新增和修改的字段

设置字段级别的校对规则

create table table_name(
id int primary key auto_increment,
name varchar(30) collate utf8_bin)
charset = utf8;

上述命令单独设置了name字段的校对结构为utf8_bin，其他字段无指定，则还是为默认的utf8_general_ci
也可在ALTER TABLE MODIFY或ALTER TABLE ADD等命令中，对已存在字段或新增字段进行单独的校对规则设置

校对规则主要是针对字符类型的，其他类型的校对类型为NULL，可通过命令SHOW FULL COLUMNS FROM table_name;进行查看

常用函数

大小写转换

SELECT UPPER(field1) as new_name1 and LOWER(field2) as new_name2 FROM table_name;

取指定数量的字符串

SELECT LEFT(field1, 4) AS left_str and RIGHT(field2, 4) AS right_str FROM table_name;      //取field1的左4位，field2的右4位
SELECT MID(field1, 1, 4) as mid_str FROM table_name;      //取field1从第1位到第4位
SELECT SUBSTRING(field1, 1, 4) as sub_str FROM table_name;      //同上
SELECT CHAR_LENGTH(field1) as length FROM table_name;      //返回field1的长度
SELECT CONCAT(field1, field2) as result1 FROM table_name;      //连接field1与field2的值

mid与substring的起始位置为1，不是0；切记与js中的substring区分开

模糊查找

SELECT * FROM table_name WHERE field1 LIKE '_h%';      //查找第二个字符为h的记录
SELECT * FROM table_name WHERE field1 REGEXP 'php|nodejs';      //查找field1包含php或nodejs的记录

REGEXP支持正则操作，包括^与$