MySQL的开发技巧2

最新推荐文章于 2024-05-20 13:15:00 发布

qq_34515002

最新推荐文章于 2024-05-20 13:15:00 发布

阅读量295

点赞数

分类专栏： MySQL 文章标签：笔记

本文链接：https://blog.csdn.net/qq_34515002/article/details/81101724

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

参考：

1、 MySQL开发技巧

2、 MySQL开发技巧2

3、MySQL开发技巧3

行转列

利用序列表处理行转列的数据

mysql> desc user1;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(30)      | NO   | MUL | NULL    |                |
| over      | varchar(50)      | YES  |     | NULL    |                |
| mobile    | varchar(100)     | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

mysql> set names 'gbk';
Query OK, 0 rows affected (0.00 sec)

mysql> update user1 set mobile='12112341234,14112341234,16112341234' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user1 set mobile='12177778888,13944445555,16188885555,18122223333' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user1 set mobile='13822225555,14177775555' where id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show create table tb_sequence\G;
*************************** 1. row ***************************
       Table: tb_sequence
Create Table: CREATE TABLE `tb_sequence` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

INSERT INTO tb_sequence VALUES
(),(),(),(),(),(),(),(),();

mysql> select user_name,REPLACE(SUBSTRING(SUBSTRING_INDEX(mobile,',',a.id),CHAR_LENGTH(SUBSTRING_IND
EX(mobile,',',a.id-1))+1),',','') AS mobile
    -> FROM tb_sequence a
    -> CROSS JOIN(
    ->  SELECT user_name,CONCAT(mobile,',') AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+
1 size
    ->  FROM user1
    -> ) b ON a.id <=b.size;
+-----------+-------------+
| user_name | mobile      |
+-----------+-------------+
| 唐僧         | 12112341234 |
| 唐僧         | 14112341234 |
| 唐僧         | 16112341234 |
| 猪八戒         | 18822225555 |
| 猪八戒         | 14188886666 |
| 孙悟空          | 12177778888 |
| 孙悟空          | 13944445555 |
| 孙悟空          | 16188885555 |
| 孙悟空          | 18122223333 |
| 沙僧        | 13822225555 |
| 沙僧        | 14177775555 |
+-----------+-------------+
11 rows in set (0.01 sec)

利用UNION的方法实现列转行

列转行的另一种场景

mysql> CREATE TABLE user1_equipment (
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   user_id  INT UNSIGNED NOT  NULL,
    ->   arms VARCHAR(10) NULL,
    ->   clothing VARCHAR(10) NULL,
    ->   shoe VARCHAR(10) NULL,
    ->   PRIMARY KEY  (id)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO user1_equipment(user_id,arms,clothing,shoe) VALUES (3,'金箍棒','锁子黄金甲','藕丝
步云履' );
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO user1_equipment(user_id,arms,clothing,shoe) VALUES (2,'九齿钉耙','僧衣','僧鞋' );

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO user1_equipment(user_id,arms,clothing,shoe) VALUES (4,'降妖宝杖','僧衣','僧鞋' );

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO user1_equipment(user_id,arms,clothing,shoe) VALUES (1,'九环锡杖','锦斓袈裟','僧鞋
' );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT user_name,'arms' as equipment, arms
    -> FROM user1 a
    -> JOIN user1_equipment b on a.id = b.user_id;
+-----------+-----------+----------+
| user_name | equipment | arms     |
+-----------+-----------+----------+
| 孙悟空          | arms      | 金箍棒         |
| 猪八戒         | arms      | 九齿钉耙       |
| 沙僧        | arms      | 降妖宝杖         |
| 唐僧         | arms      | 九环锡杖        |
+-----------+-----------+----------+
4 rows in set (0.01 sec)

mysql> SELECT user_name,'arms' as equipment, arms
    -> FROM user1 a
    -> JOIN user1_equipment b on a.id = b.user_id
    -> UNION
    -> SELECT user_name,'clothing' as equipment, clothing
    -> FROM user1 a
    -> JOIN user1_equipment b on a.id = b.user_id
    -> UNION
    -> SELECT user_name,'shoe' as equipment, shoe
    -> FROM user1 a
    -> JOIN user1_equipment b on a.id = b.user_id;
+-----------+-----------+------------+
| user_name | equipment | arms       |
+-----------+-----------+------------+
| 孙悟空          | arms      | 金箍棒           |
| 猪八戒         | arms      | 九齿钉耙         |
| 沙僧        | arms      | 降妖宝杖           |
| 唐僧         | arms      | 九环锡杖          |
| 孙悟空          | clothing  | 锁子黄金甲         |
| 猪八戒         | clothing  | 僧衣          |
| 沙僧        | clothing  | 僧衣          |
| 唐僧         | clothing  | 锦斓袈裟           |
| 孙悟空          | shoe      | 藕丝步云履         |
| 猪八戒         | shoe      | 僧鞋         |
| 沙僧        | shoe      | 僧鞋         |
| 唐僧         | shoe      | 僧鞋         |
+-----------+-----------+------------+
12 rows in set (0.00 sec)

使用序列化表的方法实现列转行

SELECT user_name, arms ,clothing,shoe
FROM user1 a
JOIN user1_equipment b on a.id = b.user_id
CROSS JOIN tb_sequence c 
WHERE c.id<=3
ORDER BY user_name;

SELECT user_name 
,case when c.id=1 then arms end
,case when c.id=2 then clothing end
,case when c.id=3 then shoe end
FROM user1 a
JOIN user1_equipment b on a.id = b.user_id
CROSS JOIN tb_sequence c 
WHERE c.id<=3
ORDER BY user_name;

SELECT user_name 
,coalesce(case when c.id=1 then arms end
,case when c.id=2 then clothing end
,case when c.id=3 then shoe end) as eq_name
FROM user1 a
JOIN user1_equipment b on a.id = b.user_id
CROSS JOIN tb_sequence c 
WHERE c.id<=3
ORDER BY user_name;

SELECT user_name 
,case when c.id=1 then 'arms'
	  when c.id=2 then 'clothing'
	  when c.id=3 then 'shoe'
 end as equipment
,coalesce(case when c.id=1 then arms end
,case when c.id=2 then clothing end
,case when c.id=3 then shoe end) as eq_name
FROM user1 a
JOIN user1_equipment b on a.id = b.user_id
CROSS JOIN tb_sequence c 
WHERE c.id<=3
ORDER BY user_name;


-- 执行结果

mysql> SELECT user_name, arms ,clothing,shoe
    -> FROM user1 a
    -> JOIN user1_equipment b on a.id = b.user_id
    -> CROSS JOIN tb_sequence c
    -> WHERE c.id<=3;
+-----------+----------+------------+------------+
| user_name | arms     | clothing   | shoe       |
+-----------+----------+------------+------------+
| 孙悟空          | 金箍棒         | 锁子黄金甲         | 藕丝步云履         |
| 猪八戒         | 九齿钉耙       | 僧衣          | 僧鞋         |
| 沙僧        | 降妖宝杖         | 僧衣          | 僧鞋         |
| 唐僧         | 九环锡杖        | 锦斓袈裟           | 僧鞋         |
| 孙悟空          | 金箍棒         | 锁子黄金甲         | 藕丝步云履         |
| 猪八戒         | 九齿钉耙       | 僧衣          | 僧鞋         |
| 沙僧        | 降妖宝杖         | 僧衣          | 僧鞋         |
| 唐僧         | 九环锡杖        | 锦斓袈裟           | 僧鞋         |
| 孙悟空          | 金箍棒         | 锁子黄金甲         | 藕丝步云履         |
| 猪八戒         | 九齿钉耙       | 僧衣          | 僧鞋         |
| 沙僧        | 降妖宝杖         | 僧衣          | 僧鞋         |
| 唐僧         | 九环锡杖        | 锦斓袈裟           | 僧鞋         |
+-----------+----------+------------+------------+
12 rows in set (0.01 sec)

mysql> SELECT user_name, arms ,clothing,shoe
    -> FROM user1 a
    -> JOIN user1_equipment b on a.id = b.user_id
    -> CROSS JOIN tb_sequence c
    -> WHERE c.id<=3
    -> ORDER BY user_name;
+-----------+----------+------------+------------+
| user_name | arms     | clothing   | shoe       |
+-----------+----------+------------+------------+
| 唐僧         | 九环锡杖        | 锦斓袈裟           | 僧鞋         |
| 唐僧         | 九环锡杖        | 锦斓袈裟           | 僧鞋         |
| 唐僧         | 九环锡杖        | 锦斓袈裟           | 僧鞋         |
| 孙悟空          | 金箍棒         | 锁子黄金甲         | 藕丝步云履         |
| 孙悟空          | 金箍棒         | 锁子黄金甲         | 藕丝步云履         |
| 孙悟空          | 金箍棒         | 锁子黄金甲         | 藕丝步云履         |
| 沙僧        | 降妖宝杖         | 僧衣          | 僧鞋         |
| 沙僧        | 降妖宝杖         | 僧衣          | 僧鞋         |
| 沙僧        | 降妖宝杖         | 僧衣          | 僧鞋         |
| 猪八戒         | 九齿钉耙       | 僧衣          | 僧鞋         |
| 猪八戒         | 九齿钉耙       | 僧衣          | 僧鞋         |
| 猪八戒         | 九齿钉耙       | 僧衣          | 僧鞋         |
+-----------+----------+------------+------------+
12 rows in set (0.00 sec)

mysql> SELECT user_name
    -> ,case when c.id=1 then arms end
    -> ,case when c.id=2 then clothing end
    -> ,case when c.id=3 then shoe end
    -> FROM user1 a
    -> JOIN user1_equipment b on a.id = b.user_id
    -> CROSS JOIN tb_sequence c
    -> WHERE c.id<=3
    -> ORDER BY user_name;
+-----------+--------------------------------+------------------------------------+-----------------
---------------+
| user_name | case when c.id=1 then arms end | case when c.id=2 then clothing end | case when c.id=3
 then shoe end |
+-----------+--------------------------------+------------------------------------+-----------------
---------------+
| 唐僧         | 九环锡杖                              | NULL                               | NULL
                         |
| 唐僧         | NULL                           | 锦斓袈裟                                   | NULL
                          |
| 唐僧         | NULL                           | NULL                               | 僧鞋
                    |
| 孙悟空          | 金箍棒                               | NULL                               | NULL
                           |
| 孙悟空          | NULL                           | 锁子黄金甲                                 | NU
LL                           |
| 孙悟空          | NULL                           | NULL                               | 藕丝步云履
                             |
| 沙僧        | 降妖宝杖                               | NULL                               | NULL
                         |
| 沙僧        | NULL                           | 僧衣                                  | NULL
                    |
| 沙僧        | NULL                           | NULL                               | 僧鞋
                   |
| 猪八戒         | 九齿钉耙                             | NULL                               | NULL
                          |
| 猪八戒         | NULL                           | 僧衣                                  | NULL
                       |
| 猪八戒         | NULL                           | NULL                               | 僧鞋
                      |
+-----------+--------------------------------+------------------------------------+-----------------
---------------+
12 rows in set (0.00 sec)

mysql> SELECT user_name
    -> ,coalesce(case when c.id=1 then arms end
    -> ,case when c.id=2 then clothing end
    -> ,case when c.id=3 then shoe end) as equipment
    -> FROM user1 a
    -> JOIN user1_equipment b on a.id = b.user_id
    -> CROSS JOIN tb_sequence c
    -> WHERE c.id<=3
    -> ORDER BY user_name;
+-----------+------------+
| user_name | equipment  |
+-----------+------------+
| 唐僧         | 九环锡杖          |
| 唐僧         | 锦斓袈裟           |
| 唐僧         | 僧鞋         |
| 孙悟空          | 金箍棒           |
| 孙悟空          | 锁子黄金甲         |
| 孙悟空          | 藕丝步云履         |
| 沙僧        | 降妖宝杖           |
| 沙僧        | 僧衣          |
| 沙僧        | 僧鞋         |
| 猪八戒         | 九齿钉耙         |
| 猪八戒         | 僧衣          |
| 猪八戒         | 僧鞋         |
+-----------+------------+
12 rows in set (0.01 sec)

mysql> SELECT user_name
    -> ,case when c.id=1 then 'arms'
    ->    when c.id=2 then 'clothing'
    ->    when c.id=3 then 'shoe'
    ->  end as equipment
    -> ,coalesce(case when c.id=1 then arms end
    -> ,case when c.id=2 then clothing end
    -> ,case when c.id=3 then shoe end) as eq_name
    -> FROM user1 a
    -> JOIN user1_equipment b on a.id = b.user_id
    -> CROSS JOIN tb_sequence c
    -> WHERE c.id<=3
    -> ORDER BY user_name;
+-----------+-----------+------------+
| user_name | equipment | eq_name    |
+-----------+-----------+------------+
| 唐僧         | arms      | 九环锡杖          |
| 唐僧         | clothing  | 锦斓袈裟           |
| 唐僧         | shoe      | 僧鞋         |
| 孙悟空          | shoe      | 藕丝步云履         |
| 孙悟空          | arms      | 金箍棒           |
| 孙悟空          | clothing  | 锁子黄金甲         |
| 沙僧        | shoe      | 僧鞋         |
| 沙僧        | arms      | 降妖宝杖           |
| 沙僧        | clothing  | 僧衣          |
| 猪八戒         | arms      | 九齿钉耙         |
| 猪八戒         | clothing  | 僧衣          |
| 猪八戒         | shoe      | 僧鞋         |
+-----------+-----------+------------+
12 rows in set (0.00 sec)

如何生成唯一序列号

需要使用唯一序列号的场景：

数据库主键

业务序列号如：发票号、车票好、订单号……

生成序列号的方法

MySQL	AUTO_INCREMENT
SQLServer	IDENTITY/SEQUENCE
Oracle	SEQUENCE
PgSQL	SEQUENCE

优先选择系统提供的序列号生成方式。

（会因为插入不成功，造成序号不连续）

mysql> CREATE TABLE t(
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY  (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t value(),();
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> -- 进行一个事务；
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t value();
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> -- 回滚事务；
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
+----+
3 rows in set (0.00 sec)

在特殊情况下可以使用SQL方式生成序列号；

用SQL方式生成特殊的序列号

需求：生成订单序列号，并且订单号的格式如下：

YYYYMMDDNNNNNNN。如201505120000003

-- 创建数据库

--
-- Table structure for table `order_seq`
--
CREATE TABLE order_seq (
  timestr INT UNSIGNED NOT NULL,
  order_sn  INT UNSIGNED NOT  NULL,
  PRIMARY KEY  (timestr,order_sn)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 创建存储过程

mysql> CREATE PROCEDURE  seq_no()
    -> BEGIN
    ->   DECLARE v_cnt INT;
    ->   DECLARE v_timestr INT;
    ->   DECLARE rowcount BIGINT;
    ->   SET v_timestr = DATE_FORMAT(NOW(),'%Y%m%d');
    ->   SELECT ROUND(RAND()*100,0)+1 INTO v_cnt;
    ->   START TRANSACTION;
    ->       UPDATE order_seq SET order_sn=order_sn+v_cnt WHERE timestr=v_timestr;
    ->       IF ROW_COUNT()=0 THEN
    ->           INSERT INTO order_seq(timestr,order_sn) VALUES (v_timestr, v_cnt);
    ->       END IF;
    ->       SELECT CONCAT(v_timestr,LPAD(order_sn,7,0)) AS order_sn
    ->       FROM order_seq
    ->       WHERE timestr = v_timestr;
    -> END
    -> //
Query OK, 0 rows affected (0.02 sec)


mysql> DELIMITER ;
mysql> call seq_no();
+-----------------+
| order_sn        |
+-----------------+
| 201808100000065 |
+-----------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> select * from order_seq;
+----------+----------+
| timestr  | order_sn |
+----------+----------+
| 20150518 |      390 |
| 20180810 |       65 |
+----------+----------+
2 rows in set (0.00 sec)

如何查询重复数据

利用Group By和 having从句处理

mysql> --
mysql> -- Table structure for table `user1_test`
mysql> --
mysql> CREATE TABLE user1_test (
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   user_name VARCHAR(30) NOT  NULL,
    ->   over VARCHAR(50)   NULL,
    ->   mobile VARCHAR(100) NULL,
    ->   PRIMARY KEY  (id)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> desc use1_test;
ERROR 1146 (42S02): Table 'try1.use1_test' doesn't exist
mysql> desc user1_test;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(30)      | NO   |     | NULL    |                |
| over      | varchar(50)      | YES  |     | NULL    |                |
| mobile    | varchar(100)     | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

-- 插入数据，产生重复数据。
mysql> INSERT INTO user1_test(user_name,over,mobile)
    -> select user_name,over,mobile from user1;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO user1_test(user_name,over,mobile)
    -> select user_name,over,mobile from user1 limit 2;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set names 'GBK';
Query OK, 0 rows affected (0.00 sec)

mysql> select user_name,count(*) from user1_test group by user_name having count(*)>1;
+-----------+----------+
| user_name | count(*) |
+-----------+----------+
| 唐僧         |        2 |
| 猪八戒         |        2 |
+-----------+----------+
2 rows in set (0.01 sec)

删除重复数据，对于相同数据保留ID最大的

DELETE a
FROM user1_test a 
JOIN(
    SELECT user_name,count(*),MAX(id) AS id
    FROM user1_test
    GROUP BY user_name
    HAVING count(*)>1
) b ON a.user_name = b.user_name
WHERE a.id<b.id;
mysql> DELETE a
    -> FROM user1_test a
    -> JOIN(
    ->     SELECT user_name,count(*),MAX(id) AS id
    ->     FROM user1_test
    ->     GROUP BY user_name
    ->     HAVING count(*)>1
    -> ) b ON a.user_name = b.user_name
    -> WHERE a.id<b.id;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from user1_test;
+----+-----------+------------+-------------------------------------------------+
| id | user_name | over       | mobile                                          |
+----+-----------+------------+-------------------------------------------------+
|  3 | 孙悟空          | 斗战圣佛         | 12177778888,13944445555,16188885555,181222
|  4 | 沙僧        | 金身罗汉          | 13822225555,14177775555
|  8 | 唐僧         | 旃檀功德佛         | 12112341234,14112341234,16112341234
|  9 | 猪八戒         | 净坛使者         | 18822225555,14188886666
+----+-----------+------------+-------------------------------------------------+
4 rows in set (0.00 sec)

mysql>

更复杂的情况

分析：

1、列转行

2、保留唯一数据的mobile;

3、行转列

4、更新。

mysql> update user1_test set mobile='12112341234,14112341234,12112341234' where id=8;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update user1_test set mobile='12177778888,13944445555,16188885555,16188885555' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user1_test;
+----+-----------+------------+-------------------------------------------------+
| id | user_name | over       | mobile                                          |
+----+-----------+------------+-------------------------------------------------+
|  3 | 孙悟空          | 斗战圣佛         | 12177778888,13944445555,16188885555,16188885555 |
|  4 | 沙僧        | 金身罗汉          | 13822225555,14177775555                         |
|  8 | 唐僧         | 旃檀功德佛         | 12112341234,14112341234,12112341234             |
|  9 | 猪八戒         | 净坛使者         | 18822225555,14188886666                         |
+----+-----------+------------+-------------------------------------------------+
4 rows in set (0.00 sec)

-- 1、列转行
mysql> select user_name,REPLACE(SUBSTRING(SUBSTRING_INDEX(mobile,',',a.id),CHAR_LENGTH(SUBSTRING_IND
EX(mobile,',',a.id-1))+1),',','') AS mobile
    -> FROM tb_sequence a
    -> CROSS JOIN(
    ->   SELECT user_name,CONCAT(mobile,',') AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))
+1 size
    ->   FROM user1_test
    -> ) b ON a.id <=b.size;
+-----------+-------------+
| user_name | mobile      |
+-----------+-------------+
| 孙悟空          | 12177778888 |
| 孙悟空          | 13944445555 |
| 孙悟空          | 16188885555 |
| 孙悟空          | 16188885555 |
| 沙僧        | 13822225555 |
| 沙僧        | 14177775555 |
| 唐僧         | 12112341234 |
| 唐僧         | 14112341234 |
| 唐僧         | 12112341234 |
| 猪八戒         | 18822225555 |
| 猪八戒         | 14188886666 |
+-----------+-------------+
11 rows in set (0.01 sec)

-- 2、保留唯一数据

-- 2-1 方法1
mysql>     SELECT id,user_name,mobile
    ->     FROM (
    ->           select b.id,user_name,REPLACE(SUBSTRING(SUBSTRING_INDEX(mobile,',',a.id),CHAR_LENGT
H(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','') AS mobile
    ->           FROM tb_sequence a
    ->           CROSS JOIN(
    ->             SELECT id,user_name,CONCAT(mobile,',') AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mo
bile,',',''))+1 size
    ->             FROM user1_test
    ->           ) b ON a.id <=b.size
    ->         ) AA
    ->     GROUP BY  id,user_name,mobile ;
+----+-----------+-------------+
| id | user_name | mobile      |
+----+-----------+-------------+
|  3 | 孙悟空          | 12177778888 |
|  3 | 孙悟空          | 13944445555 |
|  3 | 孙悟空          | 16188885555 |
|  4 | 沙僧        | 13822225555 |
|  4 | 沙僧        | 14177775555 |
|  8 | 唐僧         | 12112341234 |
|  8 | 唐僧         | 14112341234 |
|  9 | 猪八戒         | 14188886666 |
|  9 | 猪八戒         | 18822225555 |
+----+-----------+-------------+
9 rows in set (0.03 sec)

-- 2-2 方法2

mysql>         SELECT DISTINCT id,user_name,mobile
    ->     FROM (
    ->           select b.id,user_name,REPLACE(SUBSTRING(SUBSTRING_INDEX(mobile,',',a.id),CHAR_LENGT
H(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','') AS mobile
    ->           FROM tb_sequence a
    ->           CROSS JOIN(
    ->             SELECT id,user_name,CONCAT(mobile,',') AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mo
bile,',',''))+1 size
    ->             FROM user1_test
    ->           ) b ON a.id <=b.size
    ->         ) AA
    -> ;
+----+-----------+-------------+
| id | user_name | mobile      |
+----+-----------+-------------+
|  3 | 孙悟空          | 12177778888 |
|  3 | 孙悟空          | 13944445555 |
|  3 | 孙悟空          | 16188885555 |
|  4 | 沙僧        | 13822225555 |
|  4 | 沙僧        | 14177775555 |
|  8 | 唐僧         | 12112341234 |
|  8 | 唐僧         | 14112341234 |
|  9 | 猪八戒         | 18822225555 |
|  9 | 猪八戒         | 14188886666 |
+----+-----------+-------------+
9 rows in set (0.02 sec)

-- 3、行转列：连接mobile;
mysql>     SELECT id,user_name,GROUP_CONCAT(mobile) mobiles
    ->     FROM(
    ->         SELECT DISTINCT id,user_name,mobile
    ->         FROM (
    ->           select b.id,user_name,REPLACE(SUBSTRING(SUBSTRING_INDEX(mobile,',',a.id),CHAR_LENGT
H(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','') AS mobile
    ->           FROM tb_sequence a
    ->           CROSS JOIN(
    ->             SELECT id,user_name,CONCAT(mobile,',') AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mo
bile,',',''))+1 size
    ->             FROM user1_test
    ->           ) b ON a.id <=b.size
    ->         ) AA
    ->     )AB
    ->     GROUP BY id,user_name
    -> ;
+----+-----------+-------------------------------------+
| id | user_name | mobiles                             |
+----+-----------+-------------------------------------+
|  3 | 孙悟空          | 16188885555,13944445555,12177778888 |
|  4 | 沙僧        | 14177775555,13822225555             |
|  8 | 唐僧         | 14112341234,12112341234             |
|  9 | 猪八戒         | 14188886666,18822225555             |
+----+-----------+-------------------------------------+
4 rows in set (0.02 sec)


-- 4、更新。

mysql> UPDATE  user1_test FA1
    -> LEFT JOIN (
    ->     SELECT id,user_name,GROUP_CONCAT(mobile) mobiles
    ->     FROM(
    ->         SELECT DISTINCT id,user_name,mobile
    ->         FROM (
    ->           select b.id,user_name,REPLACE(SUBSTRING(SUBSTRING_INDEX(mobile,',',a.id),CHAR_LENGT
H(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','') AS mobile
    ->           FROM tb_sequence a
    ->           CROSS JOIN(
    ->             SELECT id,user_name,CONCAT(mobile,',') AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mo
bile,',',''))+1 size
    ->             FROM user1_test
    ->           ) b ON a.id <=b.size
    ->         ) AA
    ->     )AB
    ->     GROUP BY id,user_name
    -> ) FA2 ON FA1.id = FA2.id
    -> SET FA1.mobile = FA2.mobiles
    -> WHERE FA1.mobile != FA2.mobiles;
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from user1_test;
+----+-----------+------------+-------------------------------------+
| id | user_name | over       | mobile                              |
+----+-----------+------------+-------------------------------------+
|  3 | 孙悟空          | 斗战圣佛         | 16188885555,13944445555,12177778888 |
|  4 | 沙僧        | 金身罗汉          | 14177775555,13822225555             |
|  8 | 唐僧         | 旃檀功德佛         | 14112341234,12112341234             |
|  9 | 猪八戒         | 净坛使者         | 14188886666,18822225555             |
+----+-----------+------------+-------------------------------------+
4 rows in set (0.00 sec)

--开始的数据

mysql> select * from user1_test;
+----+-----------+------------+-------------------------------------------------+
| id | user_name | over       | mobile                                          |
+----+-----------+------------+-------------------------------------------------+
| 3 | 孙悟空          | 斗战圣佛         | 12177778888,13944445555,16188885555,16188885555 |
| 4 | 沙僧        | 金身罗汉          | 13822225555,14177775555                         |
| 8 | 唐僧         | 旃檀功德佛         | 12112341234,14112341234,12112341234             |
| 9 | 猪八戒         | 净坛使者         | 18822225555,14188886666                         |
+----+-----------+------------+-------------------------------------------------+

--最后的数据：重复去除，但是mobile的顺序变掉了。

mysql> select * from user1_test;
+----+-----------+------------+-------------------------------------+
| id | user_name | over       | mobile                              |
+----+-----------+------------+-------------------------------------+
| 3 | 孙悟空          | 斗战圣佛         | 16188885555,13944445555,12177778888 |
| 4 | 沙僧        | 金身罗汉          | 14177775555,13822225555             |
| 8 | 唐僧         | 旃檀功德佛         | 14112341234,12112341234             |
| 9 | 猪八戒         | 净坛使者         | 14188886666,18822225555             |
+----+-----------+------------+-------------------------------------+
4 rows in set (0.00 sec)

qq_34515002

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL的开发技巧2

参考：1、 MySQL开发技巧2、 MySQL开发技巧23、MySQL开发技巧3 行转列利用序列表处理行转列的数据mysql&gt; desc user1;+-----------+------------------+------+-----+---------+----------------+| Field | Type ...
复制链接

扫一扫

专栏目录