一. 数值类型
1. 概览
类型 字节 取值范围
tinyint 1 带符号：[-128, 127]
不带符号：[0, 255]
smallint 2 带符号：[-32768, 32767]
不带符号：[0, 65535]
mediumint 3 带符号：[-8388608, 8388607]
不带符号：[0, 16777215]
int 4 带符号：[-2147483648, 2147483647]
不带符号：[0, 4294967295]
bigint 8 带符号：[-9223372036854775808, 9223372036854775807]
不带符号：[0, 18446744073709551615]
2. tinyint类型
类型大小：1字节
带符号时的数据范围：[-128, 127]
不带符号时的数据范围：[0, 255]
示例：
有符号的tinyint类型字段：
mysql> create table if not exists t1( -> num tinyint); Query OK, 0 rows affected (0.02 sec) // 127在规定的范围内，插入成功 mysql> insert into t1 values(127); Query OK, 1 row affected (0.01 sec) // 128大于规定的范围，插入失败 mysql> insert into t1 values(128); ERROR 1264 (22003): Out of range value for column 'num' at row 1 // 插入结果查询 mysql> select * from t1; +------+ | num | +------+ | 127 | +------+ 1 row in set (0.00 sec)
无符号的tinyint类型字段： mysql> create table if not exists t2( -> num tinyint unsigned); Query OK, 0 rows affected (0.03 sec) // 255在范围内，插入成功 mysql> insert into t2 values (255); Query OK, 1 row affected (0.00 sec) // 256大于规定的数据范围，插入失败 mysql> insert into t2 values (256); ERROR 1264 (22003): Out of range value for column 'num' at row 1 // 插入结果查询 mysql> select * from t2; +------+ | num | +------+ | 255 | +------+ 1 row in set (0.01 sec)
说明：
在MySQL中，数值类型都可以指定是有符号的还是无符号的，默认是有符号的。可以在数值类型后面加上unsigned来说明某个字段是无符号的。
不要因为范围的原因而使用unsigned。比如对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。
MySQL对于数据的存储，本身具有很严格的约束。说准确点，MySQL数据类型本身就是一种约束。
3. bit类型
基本语法：
bit(M)：位字段类型。M表示所能存储值的最大二进制位数，范围从1到64。如果M被忽略，默认为1。
示例：
原因在于：bit类型的字段在显示时，是按照ASCII码对应的值来显示的，而ASCLL码为10对应的是一个控制字符，自然无法显示。下面我们插入65这个数字，看看这次能否显示出来：
mysql> select * from t1; +------+------+ | id | a | +------+------+ | 10 | | +------+------+ 1 row in set (0.00 sec) mysql> insert into t1 values (65, 65); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+------+ | id | a | +------+------+ | 10 | | | 65 | A | +------+------+ 2 rows in set (0.00 sec)
可以看到65数字插入a字段后，显示出来的是字符’A’，确实说明了bit类型的字段在显示时，是按照ASCII码对应的值来显示的。
作为一种数据类型，bit也有对应的数据范围约束，即我们插入的数据其二进制长度不能超过M：
mysql> create table if not exists t2( -> gender bit(1) comment '0:男, 1:女'); Query OK, 0 rows affected (0.02 sec) // 0的二进制：0 --- 插入成功 mysql> insert into t2 values (0); Query OK, 1 row affected (0.02 sec) // 2的二进制：10 --- 插入失败 mysql> insert into t2 values (2); ERROR 1406 (22001): Data too long for column 'gender' at row 1
bit使用场景：实际中，如果我们有这样的字段，其值只存放0或1，这时可以把它定义为bit(1)类型。缺点是数据无法被显示出来，但是这样可以节省空间；如果这张表结构简单且只是交给机器识别的话，我们可以定义bit类型的字段。
4. float类型
语法：
float(m, d) [unsigned]: M指定显示长度，d指定小数位数，占用空间4个字节空间。
注意：MySQL在保存浮点数时会进行四舍五入。
示例：
float(4, 2)表示的数据范围是[-99.99 ~ 99.99]，下面我们来看看该类型的约束情况：
mysql> create table if not exists t1( -> salary float(4, 2)); Query OK, 0 rows affected (0.03 sec) mysql> insert into t1 values (99.99);// 在规定范围之内，插入成功 Query OK, 1 row affected (0.01 sec) mysql> insert into t1 values (99.993);// 四舍后插入成功 Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (99.995);// 五入后插入失败 ERROR 1264 (22003): Out of range value for column 'salary' at row 1 mysql> insert into t1 values (100.00);// 本身超过数据类型规定的范围，插入失败 ERROR 1264 (22003): Out of range value for column 'salary' at row 1 mysql> select * from t1;// 最终只有前两个插入成功 +--------+ | salary | +--------+ | 99.99 | | 99.99 | +--------+ 2 rows in set (0.00 sec)
对浮点数而言，无符号类型的浮点数还是经常用到的，比如：工资、得分等。如果定义的类型是float(4, 2) unsigned，其能存储的数据范围是[0 ~ 99.99]。
mysql> create table if not exists t2( -> score float(4, 2) unsigned); Query OK, 0 rows affected (0.02 sec) // 在范围内，插入成功 mysql> insert into t2 values (28.4); Query OK, 1 row affected (0.00 sec) // 负数不在规定范围内，插入失败 mysql> insert into t2 values (-23.3);// 不允许插入负数 ERROR 1264 (22003): Out of range value for column 'score' at row 1 // 查询插入结果 mysql> select * from t2; +-------+ | score | +-------+ | 28.40 | +-------+ 1 row in set (0.01 sec)
5. decimal类型
语法：
decimal[(m, d)] [unsigned]：m指定长度，d表示小数点的位数。不指定时默认decimal(10, 0)。
用法：
用法上decimal与float别无二致。
decimal(5,2) 表示的范围是 -999.99 ~ 999.99
ecimal(5,2) unsigned 表示的范围 0 ~ 999.99
float 和 decimal的区别
float和decimal的区别在于二者表示的有效数字位数不同：
float能表示的有效数字位数是7位。
decimal能表示的有效数字位数是38位。
使用场景：如果希望小数的精度高，推荐使用decimal。
二. 字符串类型
1. char类型
语法：
char(len): 固定长度字符串，len表示可以存储的字符长度，最大值可为255。
说明：
1、定义char类型字段时，最大长度不能超过255，否则会报错：
mysql> create table if not exists t1( -> name char(256)); ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead
2、固定长度的字符串，其长度本身也是一种约束：
mysql> create table if not exists t1( -> name char(2));// name字段限定最多存储两个字符 Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 values ('a');// 只有一个字符，插入成功 Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values ('ab');// 两个字符，插入成功 Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values ('abc');// 三个字符，插入失败，超过了name字段限定的长度 ERROR 1406 (22001): Data too long for column 'name' at row 1 mysql> select * from t1; +------+ | name | +------+ | a | | ab | +------+ 2 rows in set (0.00 sec)
3、MySQL中的字符指的是：一个字母、一个符号或一个汉字，这些字符不一定非得是1个字节，也可能是2字节或3字节，具体多大与字符集的种类有关。即MySQL限定“字符”这个概念不是凭借字节大小，这有一个非常大的好处，就是不需要让用户再关心复杂的编码的细节了，区别C/C++中的char类型，在哪里一个字符的大小是固定的一个字节。
mysql> create table if not exists t2( -> name char(2)); Query OK, 0 rows affected (0.03 sec) mysql> insert into t2 values ('中');// 一个字符，插入成功 Query OK, 1 row affected (0.00 sec) mysql> insert into t2 values ('中国');// 两个字符，插入成功 Query OK, 1 row affected (0.00 sec) mysql> insert into t2 values ('中国人');// 三个字符，插入失败 ERROR 1406 (22001): Data too long for column 'name' at row 1 mysql> select * from t2; +--------+ | name | +--------+ | 中 | | 中国 | +--------+ 2 rows in set (0.00 sec) mysql>
4、在MySQL中，char(len)类型也叫作固定长度字符串，这里的“固定长度”指的是直接分配给你长度为len字符所需的空间，实际你用了多少字符那是你的事，具体一个字符占多少字节那是字符集种类决定的。
2. varchar类型
varchar(len)：可变长度字符串，len表示最大允许存储的字符长度，规定最大允许存储65535个字节大小的字符，具体最大字符个数由字符集种类决定。
varchar能存储的最大字符个数
varchar能存储数据的字节大小范围是[0, 65535]，其中需要用到1 - 3个字节的空间来记录当前存储的字符个数，所以说最大有效字节空间是65532。
1、当我们表的字符集编码是utf8时，varchar(n)中参数n的最大值是65533/3=21844（因为utf8中，一个字符占用3个字节，需要2个字节来记录所存储的字符个数）：
2、如果字符集编码是gbk的话，varchar(n)的参数n最大是65533/2=32766（因为gbk中，一个字符占用2字节，另外需要2个字节来记录所存储字符个数）：
char和varchar比较
相同点：二者最大存储的字符长度不能超过定义时给定的值len。
不同点：
char(len)按len的大小以及字符集种类来决定该字段需要多少内存空间。
varchar(len)按实际存储的字符个数以及字符集种类决定该字段需要多少内存空间。
示例：
如何选择定长或变长字符串？
如果字段确定好了固定的长度，就使用定长（char），比如：身份证号，手机号等。
如果字段的长度有变化，就使用变长(varchar)，比如：名字，地址等。但是你要保证最长的能存的进去。
定长字符串的磁盘空间比较浪费，但是读取效率高。因为它直接读取len字符长度大小的内存空间就行。
变长的磁盘空间比较节省，但是效率低。因为读取之前要事先确定目前存储了多少有效字符。
定长的意义是，直接开辟好对应的内存空间。
变长的意义是，在不超过自定义长度len的情况下，实际用多少字符，就开辟多少空间。
三. 时间日期类型
常用的日期类型有如下三种：
date：格式 ‘yyyy-mm-dd’ 。仅表示日期，占用三字节。
datetime：格式 ‘yyyy-mm-dd HH:ii:ss’ 。表示日期时间，占用八字节。
timestamp：表示时间戳，该字段的数据不需要显示地插入，MySQL会自动补上当前的日期时间且表中该行记录的其他字段内容发送改变时，这个时间戳字段的数值也会相应更新。
举例：
// 1、创建一张表示生日信息的表 mysql> create table if not exists birthday( -> t1 date, -> t2 datetime, -> t3 timestamp); Query OK, 0 rows affected (0.03 sec) // 2、向表中插入一条数据 mysql> insert into birthday (t1, t2) values ('2001-07-29', '2001-07-29 00:00:00'); Query OK, 1 row affected (0.00 sec) // 3、插入结果查询 mysql> select * from birthday; +------------+---------------------+---------------------+ | t1 | t2 | t3 | +------------+---------------------+---------------------+ | 2001-07-29 | 2001-07-29 00:00:00 | 2022-05-28 13:05:11 | +------------+---------------------+---------------------+ 1 row in set (0.00 sec) // 4、过一段时间后更改数据信息，发现时间戳字段的数据自动更新了 mysql> update birthday set t1='1999-07-29'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from birthday; +------------+---------------------+---------------------+ | t1 | t2 | t3 | +------------+---------------------+---------------------+ | 1999-07-29 | 2001-07-29 00:00:00 | 2022-05-28 13:08:45 | +------------+---------------------+---------------------+ 1 row in set (0.00 sec)
四. enum和set
枚举：
enum：枚举，“单选”类型。该设定提供了若干个选项的值，最终一个单元格中实际只存储了其中一个值。出于效率考虑，这些值实际存储的是“数字”，因为这些选项中每个选项的值依次对应如下数字：1,2,3,…最多65535个，所以我们在添加枚举值时，也可以用数字编号去代替具体的选项值。
枚举语法：enum(‘选项1’, ‘选项2’, ‘选项3’,…);
集合：
set：集合，“多选”类型。该设定提供了若干个选项的值，最终一个单元格中可存储其中任意多个值。出于效率考虑，这些值实际存储的仍是“数字”；最后按顺序每个选项值依次对应到如下数字：1,2,4,8,16,32，… 最多64个。
集合语法：set(‘选项值1’, ‘选项值2’, ‘选项值3’, …);
PS：在向set类型的字段插入数据时，把需要的选项统一写到一个共同的单引号中用逗号隔开，且逗号后面不能有空格。
举例：
如果你给出了enum的范围，那在插入时只能多选一，且插入的字段必须在enum中出现。
如果你限定了set的范围，那么可以多选多，插入的字段必须在set中出现。
PS：如果不满足上述要求，MySQL会直接终止你的SQL语句，这也是一种约束的体现。
mysql> create table if not exists Hero( -> name varchar(4), -> gender enum('男', '女'), -> property set('战士', '法师', '坦克', '刺客', '射手', '辅助')); Query OK, 0 rows affected (0.02 sec) mysql> insert into Hero values ('赵云', '男', '战士,刺客'); Query OK, 1 row affected (0.01 sec) mysql> insert into Hero values ('钟馗', '男', '辅助,法师'); Query OK, 1 row affected (0.00 sec) mysql> select * from Hero; +--------+--------+---------------+ | name | gender | property | +--------+--------+---------------+ | 赵云 | 男 | 战士,刺客 | | 钟馗 | 男 | 法师,辅助 | +--------+--------+---------------+ 2 rows in set (0.00 sec)
关于插入数据的一些说明
1、在向enum类型的字段中插入数据的时候，我们可以采用数字的方案：1,2,3,4…，这些数字可以看做是每一个选项的下标，下标对应的的内容就是enum中一个一个枚举出来的元素。
2、使用数字的方案向set类型的字段中插入数据，不同于enum这时下标代表的是一个指数，具体2^n才是代表元素的数值，这个下标从0开始。
总结：不推荐在插入枚举、集合数据的时候采用数字的方式代替选项值，因为这样会导致SQL语句的可读性变差，且维护起来很困难。
补充：查询包含某个集合元素记录的方法
1、广泛查询
这里需要介绍一下查询相关的find_in_set函数。
find_in_set(sub, str_list)：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；其中str_list 里的元素用逗号分隔：
mysql> select find_in_set('a', 'a,b,c'); +---------------------------+ | find_in_set('a', 'a,b,c') | +---------------------------+ | 1 | +---------------------------+ 1 row in set (0.00 sec) mysql> select find_in_set('c', 'a,b,c'); +---------------------------+ | find_in_set('c', 'a,b,c') | +---------------------------+ | 3 | +---------------------------+ 1 row in set (0.00 sec) mysql> select find_in_set('d', 'a,b,c'); +---------------------------+ | find_in_set('d', 'a,b,c') | +---------------------------+ | 0 | +---------------------------+ 1 row in set (0.00 sec)
查询Hero表中，属性包含“辅助”的英雄：
2、严格查询：select * from 表名 where 集合字段='需要查询的选项';
