MySQL字段內(nèi)容拆分及合并
1. 創(chuàng)建測試表及數(shù)據(jù)
--?創(chuàng)建一張tb_stu表，CREATE TABLE tb_user(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10) COMMENT '人名',c_no VARCHAR(64) COMMENT '持劍ID，以逗號分隔');INSERT INTO tb_user(NAME,c_no) VALUES('蘧伯玉','1,3,5,7');INSERT INTO tb_user(NAME,c_no) VALUES('高漸離','1,2,4,8,5');INSERT INTO tb_user(NAME,c_no) VALUES('樗里疾','2,9');INSERT INTO tb_user(NAME,c_no) VALUES('澹臺滅明','1,2');INSERT INTO tb_user(NAME,c_no) VALUES('鐘子期','1,2,6,8,7,3,5');INSERT INTO tb_user(NAME,c_no) VALUES('柳下惠','2,4,3,5');INSERT INTO tb_user(NAME,c_no) VALUES('百里奚','1,9');INSERT INTO tb_user(NAME,c_no) VALUES('闞止','1,6,7');INSERT INTO tb_user(NAME,c_no) VALUES('霍去病','1,8,5');INSERT INTO tb_user(NAME,c_no) VALUES('慕容白曜','1,2,3,4,5,7');INSERT INTO tb_user(NAME,c_no) VALUES('魚幼薇','7,8,9');INSERT INTO tb_user(NAME,c_no) VALUES('宋玉','6,5');
--?創(chuàng)建一張劍名create table tb_sword(id int primary key AUTO_INCREMENT,c_name varchar(4)) comment '劍名';insert into tb_sword(c_name)values('軒轅');insert into tb_sword(c_name)values('湛盧');insert into tb_sword(c_name)values('赤霄');insert into tb_sword(c_name)values('太阿');insert into tb_sword(c_name)values('七星龍淵');insert into tb_sword(c_name)values('干將');insert into tb_sword(c_name)values('莫邪');insert into tb_sword(c_name)values('魚腸');insert into tb_sword(c_name)values('純鈞');
2.??數(shù)據(jù)拆分及合并
需求：?使用一條SQL獲得tb_user表中每個人持有的劍名（劍名用“|”分隔），即得到如下結(jié)果
拆解需求：??
1） 先將tb_user表中的c_no按逗號拆分
2）將拆分后c_no中的各個id與tb_sword中的id關(guān)聯(lián)，獲取劍名
3)? 最后將每一個user對應(yīng)的劍名合并成一個字段
分段SQL如下：
步驟1：
每一個user的c_no按逗號拆分為對應(yīng)的c_id，此方法需借助于mysql.help_topic表
SELECT a.id,a.name,a.c_no,SUBSTRING_INDEX( SUBSTRING_INDEX( a.c_no, ',', b.help_topic_id + 1 ), ',',- 1 ) c_idFROM tb_user aJOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.c_no ) - LENGTH( REPLACE ( a.c_no, ',', '' ) ) + 1 )ORDER BY a.id
結(jié)果如下：
步驟2：關(guān)聯(lián)獲取每個id對應(yīng)的劍名
SELECT a2.id,a2.name,a2.c_no,a2.c_id,b2.c_nameFROM (SELECT a.id,a.name,a.c_no,SUBSTRING_INDEX( SUBSTRING_INDEX( a.c_no, ',', b.help_topic_id + 1 ), ',',- 1 ) c_idFROM tb_user aJOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.c_no ) - LENGTH( REPLACE ( a.c_no, ',', '' ) ) + 1 )ORDER?BY?a.id)?a2,???--?a2表即步驟1中拆分的結(jié)果tb_sword b2WHERE a2.c_id =b2.id -- 關(guān)聯(lián)，相當于inner join（或者join）
結(jié)果如下
步驟3：
將每個人的劍名合并為1個字段顯示，并用"|" 符合合并
SELECT a2.id,a2.name,a2.c_no,GROUP_CONCAT(b2.c_name?SEPARATOR?'|'?)?sword_name??--?SEPARATOR?指定分隔富，不加默認為逗號分隔FROM (SELECT a.id,a.name,a.c_no,SUBSTRING_INDEX( SUBSTRING_INDEX( a.c_no, ',', b.help_topic_id + 1 ), ',',- 1 ) c_idFROM tb_user aJOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.c_no ) - LENGTH( REPLACE ( a.c_no, ',', '' ) ) + 1 )ORDER BY a.id) a2,tb_sword b2WHERE a2.c_id =b2.idGROUP BY a2.id
結(jié)果如下：
實現(xiàn)需求
1.??MySQL高可用之MHA集群部署
2.??mysql8.0新增用戶及加密規(guī)則修改的那些事
3.? 比hive快10倍的大數(shù)據(jù)查詢利器-- presto
4. ?監(jiān)控利器出鞘：Prometheus+Grafana監(jiān)控MySQL、Redis數(shù)據(jù)庫
5.? PostgreSQL主從復制--物理復制
6.??MySQL傳統(tǒng)點位復制在線轉(zhuǎn)為GTID模式復制
評論
圖片
表情