/* 创建一个包含多值字段的表,大家以个人信息表为例 */ CREATE TABLE `person_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `age` int(3) NOT NULL, `hobby` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; /* 插入多值字段的数据 */ INSERT INTO `person_info` (`name`, `age`, `hobby`) VALUES ('小明', 20, '足球,篮球'), ('小红', 18, '美食, 唱歌, 跳舞'), ('小王', 22, '电影,游戏,阅读');
大家可以看到,每个人的爱好都是以逗号隔开的,这种存储方式是不利于使用的。大家需要将每个人的爱好拆分成单个条目,并将其存储在一行中。
/* 将多值字段拆分成多行 */ SELECT id, name, age, SUBSTRING_INDEX(SUBSTRING_INDEX(hobby, ',', numbers.n), ',', -1) hobby FROM person_info CROSS JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers WHERE CHAR_LENGTH(hobby) - CHAR_LENGTH(REPLACE(hobby, ',', '')) >= numbers.n - 1;
上面的代码中,大家使用了CROSS JOIN来在每个人的记录中生成多个条目,然后使用SUBSTRING_INDEX函数将每个人的爱好拆分成单个条目,这将使大家在结果集中有多个行来代表每个人的多个爱好。在这里,大家使用了一个子查询来生成要生成的条目数,这里生成的是4个条目,您可以根据实际需要增加或减少生成的数量。
现在,大家已经成功地将多值字段拆分成多行,并且可以更方便地使用数据了。