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、如何删除某个字段为空值时的记录。