【MySQL数据类型】

前言

剑指offer：一年又4天

数据类型分类

整数类型

tinyint

整数类型都分为有符号和无符号两种，默认是有符号的，在类型后加上unsigned为无符号类型；
tiinyint大小为一个字节，有符号取值范围为-128 ~ 127，无符号取值范围为0 ~ 255；

mysql> create table tb1(num tinyint);       --- num列数据为tinyint类型
Query OK, 0 rows affected (0.03 sec)

mysql> show create table tb1\G
*************************** 1. row ***************************
       Table: tb1
Create Table: CREATE TABLE `tb1` (
  `num` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tb1(num) values(-128);        --- 左边界测试
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb1(num) values(127);         --- 右边界测试
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb1(num) values(0);      	 --- 中间值
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb1(num) values(-129);        --- 越界测试
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into tb1(num) values(128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into tb1(num) values(999);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

mysql> select * from tb1;                     --- 越界值不会插入，也就是说已经插入的值都是合法的
+------+
| num  |
+------+
| -128 |
|  127 |
|    0 |
+------+
3 rows in set (0.00 sec)



ysql> create table tb2(num tinyint unsigned);            --- 无符号tinyint测试
Query OK, 0 rows affected (0.04 sec)

mysql> show create table tb2\G
*************************** 1. row ***************************
       Table: tb2
Create Table: CREATE TABLE `tb2` (
  `num` tinyint(3) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tb2(num) values(0);            --- 左边界测试
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb2(num) values(255);            --- 右边界测试
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb2(num) values(99);            --- 中间值测试
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb2(num) values(-1);            ---  越界测试
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into tb2(num) values(-128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into tb2 values(256);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

mysql> select * from tb2;                    --- 越界值不会插入
+------+
| num  |
+------+
|    0 |
|  255 |
|   99 |
+------+
3 rows in set (0.00 sec)

bit

语法：

bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。

按比特位来分配，最多可分配64个比特位，默认为1bit；

示例：

mysql> alter table tb2 add num2 bit(1);       --- 添加一列num2，类型为bit
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb2\G
*************************** 1. row ***************************
       Table: tb2
Create Table: CREATE TABLE `tb2` (
  `num` tinyint(3) unsigned DEFAULT NULL,
  `num2` bit(1) DEFAULT NULL                     --- 添加成功
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tb2(num, num2) values(1, 0); 
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb2(num, num2) values(1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb2(num, num2) values(1, 2);     --- 一个bit位只能写入0，1
ERROR 1406 (22001): Data too long for column 'num2' at row 1
mysql> insert into tb2(num, num2) values(1, 3);
ERROR 1406 (22001): Data too long for column 'num2' at row 1
mysql> insert into tb2(num, num2) values(1, -1);
ERROR 1406 (22001): Data too long for column 'num2' at row 1
mysql> select *from tb2;
+------+------+
| num  | num2 |
+------+------+
|    0 | NULL |       --- 没有写入的为空
|  255 | NULL |
|   99 | NULL |
|    1 |      |       --- 我们明明写入了为什么看不到？
|    1 |     |
+------+------+
5 rows in set (0.00 sec)

看不到的原因：bit类型的数据会以它的ASCII码来显示，这里0和1的ASCII码对应的是不可显示字符；
验证：

mysql> alter table tb2 modify num2 bit(10);        ---   修改bit位个数，增加到10
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show create table tb2\G
*************************** 1. row ***************************
       Table: tb2
Create Table: CREATE TABLE `tb2` (
  `num` tinyint(3) unsigned DEFAULT NULL,
  `num2` bit(10) DEFAULT NULL                     --- 修改成功
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tb2 values(2, 97);             ---  插入数字97
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb2 values(2, 'a');            --- 插入字符 'a'
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb2;
+------+------+
| num  | num2 |
+------+------+
|    0 | NULL |
|  255 | NULL |
|   99 | NULL |
|    1 |      |
|    1 |     |
|    2 |  a   |           --- a的ASCII码就是97
|    2 |  a   |
+------+------+
7 rows in set (0.00 sec)


mysql> alter table tb2 modify num2 int;         --- 修改num2列的类型，由bit(10)改为int
Query OK, 7 rows affected (0.10 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from tb2;                     ---  再次查看
+------+------+
| num  | num2 |
+------+------+
|    0 | NULL |
|  255 | NULL |
|   99 | NULL |
|    1 |    0 |                             ---  整形数据
|    1 |    1 |
|    2 |   97 |
|    2 |   97 |
+------+------+
7 rows in set (0.00 sec)

小数类型

float

语法：

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节

示例：

mysql> create table tb3(
    -> name varchar(32),
    -> score float(4,2)                --- float(长度为4，小数位占2位，范围：-99.99 ~ 99.99）
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table tb3\G
*************************** 1. row ***************************
       Table: tb3
Create Table: CREATE TABLE `tb3` (
  `name` varchar(32) DEFAULT NULL,
  `score` float(4,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tb3 values('陈平安', -99.99);     --- 左极限测试
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb3 values('宁姚', 99.99);     	--- 右极限测试
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb3 values('小米粒', 0);     		--- 中间值测试
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb3 values('陈灵均', 100.00);  	---  越界测试
ERROR 1264 (22003): Out of range value for column 'score' at row 1

mysql> insert into tb3 values('周肥', -100.00);
ERROR 1264 (22003): Out of range value for column 'score' at row 1

mysql> select * from tb3; 
+-----------+--------+
| name      | score  |
+-----------+--------+
| 陈平安    | -99.99 |
| 宁姚      |  99.99 |
| 小米粒    |   0.00 |
+-----------+--------+
3 rows in set (0.00 sec)

扩展1：

mysql> insert into tb3 values('陈如初', 99.994);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb3 values('裴钱', 99.992);
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb3 values('老厨子', 99.787998);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb3 values('郑大风', 99.995);         
ERROR 1264 (22003): Out of range value for column 'score' at row 1
mysql> select * from tb3;
+-----------+--------+
| name      | score  |
+-----------+--------+
| 陈平安    | -99.99 |
| 宁姚      |  99.99 |
| 小米粒    |   0.00 |    --- 0
| 陈如初    |  99.99 |    --- 99.994
| 裴钱      |  99.99 |    --- 99.992
| 老厨子    |  99.79 |    --- 99.787998
+-----------+--------+   ---  99.995  插入失败
6 rows in set (0.00 sec)

由此可见，float的位数约束虽然严格，但并不如整数那样一点也不能越界，当小数位不足时会自动补0，小数位过多时会进行四舍五入，
99.995四舍五入变为100.00 ，不符合float(4, 2) 四位的约束，因此插入失败，其他的99.994, 99.992等会被舍去，不会越界。

扩展2：

mysql> create table tb4(
    -> name varchar(32),
    -> score float(4, 2) unsigned);           --- 测试无符号float取值范围
Query OK, 0 rows affected (0.05 sec)

mysql> show create table tb4\G
*************************** 1. row ***************************
       Table: tb4
Create Table: CREATE TABLE `tb4` (
  `name` varchar(32) DEFAULT NULL,
  `score` float(4,2) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tb4 values ('陈平安', -99.99);            --- 越界
ERROR 1264 (22003): Out of range value for column 'score' at row 1

mysql> insert into tb4 values ('宁姚', 99.99);              
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb4 values ('小米粒', 0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb4 values ('陈灵均', 100);              --- 越界
ERROR 1264 (22003): Out of range value for column 'score' at row 1

mysql> insert into tb4 values ('周肥', -1);               --- 越界
ERROR 1264 (22003): Out of range value for column 'score' at row 1

mysql> select * from tb4;
+-----------+-------+
| name      | score |
+-----------+-------+
| 宁姚      | 99.99 |
| 小米粒    |  0.00 |
+-----------+-------+
2 rows in set (0.00 sec)

结论：无符号float并不像无符号int那样取值范围右移，而是直接在原范围上删去负数部分。

decimal

语法：

decimal (m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

decimal(5,2) 表示的范围是 -999.99 ~ 999.99
decimal(5,2) unsigned 表示的范围 0 ~ 999.99
decimal和float很像，但是有区别:
float和decimal表示的精度不一样

mysql> create table if not exists tb5(
    -> num1 float(10, 8),                   --- -99.**** ~ 99.****
    -> num2 decimal(10, 8)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show create table tb5\G
*************************** 1. row ***************************
       Table: tb5
Create Table: CREATE TABLE `tb5` (
  `num1` float(10,8) DEFAULT NULL,
  `num2` decimal(10,8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tb5 values(10, 10);                           --- 测试1
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb5 values(10.1234, 10.1234);                  --- 测试2
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb5 values(10.12345678, 10.12345678);          --- 测试3
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb5 values(10.123456789, 10.123456789);        --- 测试4
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into tb5 values(100, 100);                       --- 越界测试
ERROR 1264 (22003): Out of range value for column 'num1' at row 1

mysql> select * from tb5;
+-------------+-------------+
| num1        | num2        |
+-------------+-------------+
| 10.00000000 | 10.00000000 |    	--- 10
| 10.12339973 | 10.12340000 |    	--- 10.1234
| 10.12345695 | 10.12345678 |    	--- 10.12345678
| 10.12345695 | 10.12345679 |    	--- 10.123456789
+-------------+-------------+
4 rows in set (0.00 sec)

我们会发现，decimal类型的数据完全且正确地保存了起来，而float类型的数据发生了精度损失。

说明：
float表示的精度大约是7位。
decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。
建议：
如果希望小数的精度高，推荐使用decimal。

字符串类型

char

语法：

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255，默认为1

示例：

mysql> create table tb6 (name char(3));            --- name列长度为三个字符
Query OK, 0 rows affected (0.03 sec)

mysql> show create table tb6\G
*************************** 1. row ***************************
       Table: tb6
Create Table: CREATE TABLE `tb6` (
  `name` char(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tb6(name) values('a');        --- 一个字符
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb6(name) values('b');        --- 一个字符
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb6(name) values('ab');        --- 两个字符
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb6(name) values('abc');       --- 三个字符
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb6(name) values('abcd');      --- 四个字符，越界
ERROR 1406 (22001): Data too long for column 'name' at row 1

mysql> insert into tb6(name) values('abcde');        --- 五个字符，越界
ERROR 1406 (22001): Data too long for column 'name' at row 1

mysql> insert into tb6(name) values('中');        --- 一个字符
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb6(name) values('中国');        --- 两个字符
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb6(name) values('中国人');       --- 三个字符
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb6(name) values('中国人加');      --- 四个字符，越界
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into tb6(name) values('中国人加油');      --- 五个字符，越界
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select * from tb6;
+-----------+
| name      |
+-----------+
| a         |
| b         |
| ab        |
| abc       |
| 中        |
| 中国      |
| 中国人    |
+-----------+
7 rows in set (0.00 sec)


mysql> alter table tb6 modify name char(255);       --- 边界测试
Query OK, 7 rows affected (0.29 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> alter table tb6 modify name char(256);      ---  越界测试
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead

结论：char的单位是字符，不伦是一个英文字母还是一个汉字等都算是一个字符（注意：是一个字符而不是一个字节）。

varchar

语法：

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节

示例：

mysql> create table tb7(name varchar(7));
Query OK, 0 rows affected (0.04 sec)

mysql> show create table tb7\G
*************************** 1. row ***************************
       Table: tb7
Create Table: CREATE TABLE `tb7` (
  `name` varchar(7) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tb7 values('aaa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb7 values('中国人,加油!');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb7;
+-------------------+
| name              |
+-------------------+
| aaa               |
| 中国人,加油!      |
+-------------------+
2 rows in set (0.00 sec)

说明：
关于varchar(len),len到底是多大，这个len值，和表的编码密切相关：
varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。
当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]，
如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。

mysql> alter table tb7 modify name varchar(65535);          --- 注意：是65535个字节，不是字符
ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead

mysql> select 21845 * 3;  ---  显然，tb7表采用的是utf8编码
+-----------+
| 21845 * 3 |
+-----------+
|    65535  |
+-----------+
1 row in set (0.00 sec)

mysql> alter table tb7 modify name varchar(21845);   --- 由于需要保留1-3个字节用来记录字符串长度，因此它上面所说的21845也会越界
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> alter table tb7 modify name varchar(21844);   --- 留出长度位（减一，留了三位）
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

如何选择定长或变长字符串？

如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
如果数据长度有变化,就使用变长(varchar),比如：名字，地址，但是你要保证最长的能存的进去。
定长的磁盘空间比较浪费，但是效率高。
变长的磁盘空间比较节省，但是效率低。
定长的意义是，直接开辟好对应的空间。
变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

日期和时间

常用的日期有如下三个：

date :日期 ‘yyyy-mm-dd’ ，占用三字节。
datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ，占用八字节。
timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节。

mysql> create table if not exists tb8( 
    -> date date,
    -> time datetime,
    -> modified timestamp
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table tb8\G
*************************** 1. row ***************************
       Table: tb8
Create Table: CREATE TABLE `tb8` (
  `date` date DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tb8(date, time) values('2003-03-13', '2003-03-13 14:02:18');  --- 写入了date列和time列
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb8;
+------------+---------------------+---------------------+
| date       | time                | modified            |    --- modified列自动写入当前时间
+------------+---------------------+---------------------+
| 2003-03-13 | 2003-03-13 14:02:18 | 2023-11-18 14:04:10 | 
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update tb8 set time = '1970-01-01 0:0:0';            --- 修改timelie数据
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb8;
+------------+---------------------+---------------------+
| date       | time                | modified            |     ---  modified列自动修改为当前时间
+------------+---------------------+---------------------+ 
| 2003-03-13 | 1970-01-01 00:00:00 | 2023-11-18 14:05:38 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

date类型可以用于身份证出生日期的记录；
datetime在需要记录更详细的信息时使用；
timestamp比如如今在网络上发表评论，就会自动带上发表时间。

enum & set

语法：

enum: 枚举，“单选”类型；

enum('选项1','选项2','选项3',...);

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,…最多65535个；
当我们添加枚举值时，也可以添加对应的数字编号。

set：集合，“多选”类型；

set('选项值1','选项值2','选项值3', ...);

该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，… 最多64个。

mysql> create table tb9(
    -> name varchar(32),
    -> gander enum('男', '女', '保密'),
    -> hobby set('代码', '登山', '跑步', '跳绳', '单车')
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table tb9\G
*************************** 1. row ***************************
       Table: tb9
Create Table: CREATE TABLE `tb9` (
  `name` varchar(32) DEFAULT NULL,
  `gander` enum('男','女','保密') DEFAULT NULL,
  `hobby` set('代码','登山','跑步','跳绳','单车') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tb9 values('张三', '男', '代码');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb9 values('李四', '女', '登山');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb9 values('王五', '男', '代码,登山');  --- set类型可以一次选多个，在引号内使用逗号分割
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb9 values('赵六', '未知', '代码,登山'); --- gander列没有‘未知’这个选项值
ERROR 1265 (01000): Data truncated for column 'gander' at row 1

mysql> insert into tb9 values('赵六', '男,保密', '代码,登山'); --- gander列为enum类型，一次只能选一个
ERROR 1265 (01000): Data truncated for column 'gander' at row 1
mysql> select * from tb9;
+--------+--------+---------------+
| name   | gander | hobby         |
+--------+--------+---------------+
| 张三   | 男     | 代码          |
| 李四   | 女     | 登山          |
| 王五   | 男     | 代码,登山     |
+--------+--------+---------------+
3 rows in set (0.00 sec)

mysql> insert into tb9 values('赵六', 1, 1);  --- enum可以使用下标进行选择（1,2,3...）,set可以使用位图进行选择(1,2,4,8...)
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb9;
+--------+--------+---------------+
| name   | gander | hobby         |
+--------+--------+---------------+
| 张三   | 男     | 代码          |
| 李四   | 女     | 登山          |
| 王五   | 男     | 代码,登山     |
| 赵六   | 男     | 代码          | 
+--------+--------+---------------+
4 rows in set (0.00 sec)

mysql> show create table tb9\G
*************************** 1. row ***************************
       Table: tb9
Create Table: CREATE TABLE `tb9` (
  `name` varchar(32) DEFAULT NULL,
  `gander` enum('男','女','保密') DEFAULT NULL,
  `hobby` set('代码','登山','跑步','跳绳','单车') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tb9 values('孙七', 2, 2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb9 values('周八', 3, 3);   --- hobby列的3为 1 + 2，既代码和登山
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb9 values('吴九', 4, 4);    --- gander列只有3个选项
ERROR 1265 (01000): Data truncated for column 'gander' at row 1
mysql> insert into tb9 values('吴九', 1, 4);
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb9 values('郑十', 1, 31);   --- 31 ： 1+2+4+8+16
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb9;
+--------+--------+------------------------------------+
| name   | gander | hobby                              |
+--------+--------+------------------------------------+
| 张三   | 男     | 代码                               |
| 李四   | 女     | 登山                               |
| 王五   | 男     | 代码,登山                          |
| 赵六   | 男     | 代码                               |
| 孙七   | 女     | 登山                               |
| 周八   | 保密   | 代码,登山                          |        --- 3
| 吴九   | 男     | 跑步                               |       --- 4
| 郑十   | 男     | 代码,登山,跑步,跳绳,单车           |          --- 31
+--------+--------+------------------------------------+
8 rows in set (0.00 sec)

mysql> show create table tb9\G
*************************** 1. row ***************************
       Table: tb9
Create Table: CREATE TABLE `tb9` (
  `name` varchar(32) DEFAULT NULL,
  `gander` enum('男','女','保密') DEFAULT NULL,
  `hobby` set('代码','登山','跑步','跳绳','单车') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

在集合中查找

mysql> select * from tb9 where gander='男';           --- 使用选项
+--------+--------+------------------------------------+
| name   | gander | hobby                              |
+--------+--------+------------------------------------+
| 张三   | 男     | 代码                               |
| 王五   | 男     | 代码,登山                          |
| 赵六   | 男     | 代码                               |
| 吴九   | 男     | 跑步                               |
| 郑十   | 男     | 代码,登山,跑步,跳绳,单车           |
+--------+--------+------------------------------------+
5 rows in set (0.00 sec)

mysql> select * from tb9 where gander=1;          --- 使用下标
+--------+--------+------------------------------------+
| name   | gander | hobby                              |
+--------+--------+------------------------------------+
| 张三   | 男     | 代码                               |
| 王五   | 男     | 代码,登山                          |
| 赵六   | 男     | 代码                               |
| 吴九   | 男     | 跑步                               |
| 郑十   | 男     | 代码,登山,跑步,跳绳,单车           |
+--------+--------+------------------------------------+
5 rows in set (0.00 sec)


mysql> select * from tb9 where hobby='登山';           --- 使用选项
+--------+--------+--------+
| name   | gander | hobby  |
+--------+--------+--------+
| 李四   | 女     | 登山   |
| 孙七   | 女     | 登山   |
+--------+--------+--------+
2 rows in set (0.00 sec)

mysql> select * from tb9 where hobby=2;          --- 使用位图数值
+--------+--------+--------+
| name   | gander | hobby  |
+--------+--------+--------+
| 李四   | 女     | 登山   |
| 孙七   | 女     | 登山   |
+--------+--------+--------+
2 rows in set (0.00 sec)

有一个问题：使用 = 只能查找完全匹配的，就是爱好只有登山的，但是如果我们想要查找爱好包含登山该如何查找呢？
解决方案：MySQL内置函数：find_in_set

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('b', 'a,b,c');
+---------------------------+
| find_in_set('b', 'a,b,c') |
+---------------------------+
|                         2 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('a,b', 'a,b,c');     --- 该函数一次只能查找一个选项，'a,b'被当做一个整体
+-----------------------------+
| find_in_set('a,b', 'a,b,c') |
+-----------------------------+
|                           0 |                 --- 0，没有找到
+-----------------------------+
1 row in set (0.00 sec)
mysql> select * from tb9 where find_in_set('登山', hobby);     --- 查找hobby包含登山的所有人
+--------+--------+------------------------------------+
| name   | gander | hobby                              |
+--------+--------+------------------------------------+
| 李四   | 女     | 登山                               |
| 王五   | 男     | 代码,登山                          |
| 孙七   | 女     | 登山                               |
| 周八   | 保密   | 代码,登山                          |
| 郑十   | 男     | 代码,登山,跑步,跳绳,单车           |
+--------+--------+------------------------------------+
5 rows in set (0.00 sec)

mysql> select * from tb9 where find_in_set('代码,登山', hobby);  --- 如果要查找爱好包含代码和登山的所有人呢？
Empty set (0.00 sec)

mysql> select * from tb9 where find_in_set('代码', hobby) and find_in_set('登山', hobby);  --- 查找两次，and表示逻辑与
+--------+--------+------------------------------------+
| name   | gander | hobby                              |
+--------+--------+------------------------------------+
| 王五   | 男     | 代码,登山                          |
| 周八   | 保密   | 代码,登山                          |
| 郑十   | 男     | 代码,登山,跑步,跳绳,单车           |
+--------+--------+------------------------------------+
3 rows in set (0.00 sec)