修改set类型，确保将插入值与定义值是一致

mysql> insert into tb_demo(hobby)values('basketball'),('volleyball,football'),('football,football,basketball');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 2

mysql> desc tb_demo;
+---------+------------------------------------------+------+-----+---------+-------+
| Field   | Type                                     | Null | Key | Default | Extra |
+---------+------------------------------------------+------+-----+---------+-------+
| id      | int(11)                                  | YES  |     | NULL    |       |
| name    | varchar(30)                              | YES  |     | NULL    |       |
| age     | tinyint(4)                               | YES  |     | NULL    |       |
| score   | float(4,1)                               | YES  |     | NULL    |       |
| sex     | enum('w','m')                            | YES  |     | NULL    |       |
| hobby   | set('football','basketball','volleyall') | YES  |     | NULL    |       |
| photo   | varbinary(255)                           | YES  |     | NULL    |       |
| spend   | decimal(5,1)                             | YES  |     | NULL    |       |
| address | json                                     | YES  |     | NULL    |       |
| intro   | text                                     | YES  |     | NULL    |       |
+---------+------------------------------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)


mysql> alter table tb_demo modify hobby set('football','basketball','volleyball');
Query OK, 18 rows affected (0.02 sec)
Records: 18  Duplicates: 0  Warnings: 0

mysql> desc tb_demo;
+---------+-------------------------------------------+------+-----+---------+-------+
| Field   | Type                                      | Null | Key | Default | Extra |
+---------+-------------------------------------------+------+-----+---------+-------+
| id      | int(11)                                   | YES  |     | NULL    |       |
| name    | varchar(30)                               | YES  |     | NULL    |       |
| age     | tinyint(4)                                | YES  |     | NULL    |       |
| score   | float(4,1)                                | YES  |     | NULL    |       |
| sex     | enum('w','m')                             | YES  |     | NULL    |       |
| hobby   | set('football','basketball','volleyball') | YES  |     | NULL    |       |
| photo   | varbinary(255)                            | YES  |     | NULL    |       |
| spend   | decimal(5,1)                              | YES  |     | NULL    |       |
| address | json                                      | YES  |     | NULL    |       |
| intro   | text                                      | YES  |     | NULL    |       |
+---------+-------------------------------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

mysql> insert into tb_demo(hobby)values('basketball'),('volleyball,football'),('football,football,basketball');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select hobby from tb_demo;
+---------------------+
| hobby               |
+---------------------+
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| basketball          |
| football,volleyball |
| football,basketball |
+---------------------+
21 rows in set (0.00 sec)

mysql> delete  from tb_demo where hobby=' ' or hobby  IS NULL ;

mysql> select hobby from tb_demo;
+---------------------+
| hobby               |
+---------------------+
| basketball          |
| football,volleyball |
| football,basketball |
+---------------------+

操作技巧：

1、在使用SET类型时，一定要按照预先定义的选项进行数据插入。如果插入了不存在的选项，将导致插入失败。

2、如何删除某个字段为空值时的记录。