mysql实战之 批量update
mysql实战之批量update
现阶段我们的业务量很小，要对admin_user表中的relationship字段进行更新，指定id是409。已知409是公司内的一服务中心，需要把该服务中心放到区代下面， 区代id是3486，相关字段是paren_id（父子级别关系），需要把409已经409旗下所有商家以及客户都转移到3486下面，这个对应字段是relationship ，业务介绍完毕，下面来实战操作：
查看各个id现在的relationship情况
mysql> SELECT relationship FROM admin_user WHERE parent_id=409;
+-------------------------+
| relationship |
+-------------------------+
| ,1,30,304,405,409,437, |
| ,1,30,304,405,409,450, |
| ,1,30,304,405,409,464, |
| ,1,30,304,405,409,465, |
| ,1,30,304,405,409,471, |
| ,1,30,304,405,409,505, |
| ,1,30,304,405,409,540, |
| ,1,30,304,405,409,793, |
| ,1,30,304,405,409,794, |
| ,1,30,304,405,409,1801, |
| ,1,30,304,405,409,1802, |
| ,1,30,304,405,409,1949, |
| ,1,30,304,405,409,2015, |
+-------------------------+
13 rows in set (0.02 sec)
mysql> SELECT relationship FROM admin_user WHERE parent_id=3486;
+---------------------+
| relationship |
+---------------------+
| ,1,30,304,3486,409, |
+---------------------+
1 row in set (0.02 sec)
现在就是要把parent_id=409 的relationship中’,1,30,304,405,409,437,’所有的409换成3486，
笨方法：一条一条的update，但是业务量小，这样可以实现，假如是几万条数据的话这样操作是不可取的，还有手动更改某一个数很难保证正确性。
UPDATE admin_user SET relationship=',1,30,304,405,3486,437,' WHERE id=437;
UPDATE admin_user SET relationship=',1,30,304,405,3486,450,' WHERE id=450;
UPDATE admin_user SET relationship=',1,30,304,405,3486,464,' WHERE id=464;
。。。。。。。。。。。
其实我就是用这样的方法去更新的，后来经过领导指点采取新的方法实现一条语句更新来实现业务，
使用到的是连接函数concat，查看语法
mysql> help concat
Name: 'CONCAT'
Description:
Syntax:
CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments. May
have one or more arguments. If all arguments are nonbinary strings, the
result is a nonbinary string. If the arguments include any binary
strings, the result is a binary string. A numeric argument is converted
to its equivalent nonbinary string form.
CONCAT() returns NULL if any argument is NULL.
URL: http://dev.mysql.com/doc/refman/5.6/en/string-functions.html
Examples:
mysql> SELECT CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
-> NULL
mysql> SELECT CONCAT(14.3);
-> '14.3'
我需要替换掉最左边之前的字符，都是固定的，先写下查询的sql语句试试，
mysql> SELECT CONCAT(',1,30,304,3486,409,',RIGHT(relationship,LENGTH(relationship)-LENGTH(',1,30,304,405,409,'))) AS r1,relationship FROM admin_user WHERE relationship LIKE ',1,30,304,405,409,%';
+--------------------------+-------------------------+
| r1 | relationship |
+--------------------------+-------------------------+
| ,1,30,304,3486,409,437, | ,1,30,304,405,409,437, |
| ,1,30,304,3486,409,450, | ,1,30,304,405,409,450, |
| ,1,30,304,3486,409,464, | ,1,30,304,405,409,464, |
| ,1,30,304,3486,409,465, | ,1,30,304,405,409,465, |
| ,1,30,304,3486,409,471, | ,1,30,304,405,409,471, |
| ,1,30,304,3486,409,505, | ,1,30,304,405,409,505, |
| ,1,30,304,3486,409,540, | ,1,30,304,405,409,540, |
| ,1,30,304,3486,409,793, | ,1,30,304,405,409,793, |
| ,1,30,304,3486,409,794, | ,1,30,304,405,409,794, |
| ,1,30,304,3486,409,1801, | ,1,30,304,405,409,1801, |
| ,1,30,304,3486,409,1802, | ,1,30,304,405,409,1802, |
| ,1,30,304,3486,409,1949, | ,1,30,304,405,409,1949, |
| ,1,30,304,3486,409,2015, | ,1,30,304,405,409,2015, |
+--------------------------+-------------------------+
13 rows in set (0.01 sec)
很显然，r1就是我想要的结果，下面可以更改成一个更新语句了，
mysql> update admin_user set relationship=concat(',1,30,304,3486,409,',RIGHT(relationship,LENGTH(relationship)-LENGTH(',1,30,304,405,409,'))) where relationship LIKE ',1,30,304,405,409,%';
Query OK, 13 rows affected (0.05 sec)
Rows matched: 13 Changed: 13 Warnings: 0
mysql> select relationship from admin_user where parent_id=409;
+--------------------------+
| relationship |
+--------------------------+
| ,1,30,304,3486,409,437, |
| ,1,30,304,3486,409,450, |
| ,1,30,304,3486,409,464, |
| ,1,30,304,3486,409,465, |
| ,1,30,304,3486,409,471, |
| ,1,30,304,3486,409,505, |
| ,1,30,304,3486,409,540, |
| ,1,30,304,3486,409,793, |
| ,1,30,304,3486,409,794, |
| ,1,30,304,3486,409,1801, |
| ,1,30,304,3486,409,1802, |
| ,1,30,304,3486,409,1949, |
| ,1,30,304,3486,409,2015, |
+--------------------------+
13 rows in set (0.02 sec)
搞定，期间我尝试过用when case来实现这个业务，跟单个update一样繁琐，就这13条数据也看不出来有多效率，所以还是这个拼接替换好用，菜鸟级水平奉上。
