Mysql数据库多表联合更新

来源：互联网发布：淘宝怎么查看粉丝是谁编辑：程序博客网时间：2024/06/08 13:40

有时数据库某些字段为字符串类型，并且内容比较长，由于字符串比较占存储空间，当数据比较多时也会影响查询效率，这时就可以把字符串移到另一个表中，并建立相应的编号，然后将字符串替换成这些int 类型的编号；

先看下修改前的表，明显goods_name 数据比较多，需要替换（由于排版问题，所以只查询其中三项）

mysql> select goods_id, goods_name,goods_cate from goods;+----------+------------------------------------------------------------------------+---------------+| goods_id | goods_name                                                             | goods_cate    |+----------+------------------------------------------------------------------------+---------------+|        1 | R510VC 15.6英寸笔记本                                                  | 笔记本        ||        2 | Y400N 14.0英寸笔记本电脑                                               | 笔记本        ||        3 | G150TH 15.6英寸游戏本                                                  | 游戏本        ||        4 | X550CC 15.6英寸笔记本                                                  | 笔记本        ||        5 | X240(20ALA0EYCD) 12.5英寸超极本                                        | 超级本        ||        6 | U330P 13.3英寸超极本                                                   | 超级本        ||        7 | SVP13226SCB 13.3英寸触控超极本                                         | 超级本        ||        8 | iPad mini MD531CH/A 7.9英寸平板电脑                                    | 平板电脑      ||        9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）                      | 平板电脑      ||       10 |  iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） | 平板电脑      ||       11 | IdeaCentre C340 20英寸一体电脑                                         | 台式机        ||       12 | Vostro 3800-R1206 台式电脑                                             | 台式机        ||       13 | iMac ME086CH/A 21.5英寸一体电脑                                        | 台式机        ||       14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）     | 台式机        ||       15 | Z220SFF F4F06PA工作站                                                  | 服务器/工作站 ||       16 | PowerEdge T110 II服务器                                                | 服务器/工作站 ||       17 | Mac Pro MD878CH/A 专业级台式电脑                                       | 服务器/工作站 ||       18 |  HMZ-T3W 头戴显示设备                                                  | 笔记本配件    ||       19 | 商务双肩背包                                                           | 笔记本配件    ||       20 | X3250 M4机架式服务器 2583i14                                           | 服务器/工作站 ||       21 |  HMZ-T3W 头戴显示设备                                                  | 笔记本配件    ||       22 | 商务双肩背包                                                           | 笔记本配件    |+----------+------------------------------------------------------------------------+---------------+22 rows in set (0.00 sec)

1，首先建立新表，将要修改的字段内容进行分组，将分组后的内容插入到新表中

mysql> create table goods_name(    -> name_id smallInt unsigned primary key auto_increment,    -> goods_name varchar(150))    -> select goods_name from goods group by goods_name;Query OK, 20 rows affected (0.02 sec)Records: 20  Duplicates: 0  Warnings: 0

下面是新建立的表goods_name

mysql> select * from goods_name;+---------+------------------------------------------------------------------------+| name_id | goods_name                                                             |+---------+------------------------------------------------------------------------+|       1 |  HMZ-T3W 头戴显示设备                                                  ||       2 |  iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） ||       3 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）     ||       4 | G150TH 15.6英寸游戏本                                                  ||       5 | IdeaCentre C340 20英寸一体电脑                                         ||       6 | iMac ME086CH/A 21.5英寸一体电脑                                        ||       7 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）                      ||       8 | iPad mini MD531CH/A 7.9英寸平板电脑                                    ||       9 | Mac Pro MD878CH/A 专业级台式电脑                                       ||      10 | PowerEdge T110 II服务器                                                ||      11 | R510VC 15.6英寸笔记本                                                  ||      12 | SVP13226SCB 13.3英寸触控超极本                                         ||      13 | U330P 13.3英寸超极本                                                   ||      14 | Vostro 3800-R1206 台式电脑                                             ||      15 | X240(20ALA0EYCD) 12.5英寸超极本                                        ||      16 | X3250 M4机架式服务器 2583i14                                           ||      17 | X550CC 15.6英寸笔记本                                                  ||      18 | Y400N 14.0英寸笔记本电脑                                               ||      19 | Z220SFF F4F06PA工作站                                                  ||      20 | 商务双肩背包                                                           |+---------+------------------------------------------------------------------------+20 rows in set (0.00 sec)

2，将要修改的表中的数据替换成新表中的id;

mysql> update goods as A inner join goods_name as B on A.goods_name = B.goods_name set A.goods_name = B.name_id;Query OK, 22 rows affected (0.01 sec)Rows matched: 22  Changed: 22  Warnings: 0

再来看下更新后的表

mysql> select * from goods;+----------+------------+---------------+------------+-------------+---------+------------+| goods_id | goods_name | goods_cate    | brand_name | goods_price | is_show | is_saleoff |+----------+------------+---------------+------------+-------------+---------+------------+|        1 | 11         | 笔记本        | 华硕       |    3399.000 |       1 |          0 ||        2 | 18         | 笔记本        | 联想       |    4899.000 |       1 |          0 ||        3 | 4          | 游戏本        | 雷神       |    8499.000 |       1 |          0 ||        4 | 17         | 笔记本        | 华硕       |    2799.000 |       1 |          0 ||        5 | 15         | 超级本        | 联想       |    4999.000 |       1 |          0 ||        6 | 13         | 超级本        | 联想       |    4299.000 |       1 |          0 ||        7 | 12         | 超级本        | 索尼       |    7999.000 |       1 |          0 ||        8 | 8          | 平板电脑      | 苹果       |    1998.000 |       1 |          0 ||        9 | 7          | 平板电脑      | 苹果       |    3388.000 |       1 |          0 ||       10 | 2          | 平板电脑      | 苹果       |    2788.000 |       1 |          0 ||       11 | 5          | 台式机        | 联想       |    3499.000 |       1 |          0 ||       12 | 14         | 台式机        | 戴尔       |    2899.000 |       1 |          0 ||       13 | 6          | 台式机        | 苹果       |    9188.000 |       1 |          0 ||       14 | 3          | 台式机        | 宏碁       |    3699.000 |       1 |          0 ||       15 | 19         | 服务器/工作站 | 惠普       |    4288.000 |       1 |          0 ||       16 | 10         | 服务器/工作站 | 戴尔       |    5388.000 |       1 |          0 ||       17 | 9          | 服务器/工作站 | 苹果       |   28888.000 |       1 |          0 ||       18 | 1          | 笔记本配件    | 索尼       |    6999.000 |       1 |          0 ||       19 | 20         | 笔记本配件    | 索尼       |      99.000 |       1 |          0 ||       20 | 16         | 服务器/工作站 | IBM        |    6888.000 |       1 |          0 ||       21 | 1          | 笔记本配件    | 索尼       |    6999.000 |       1 |          0 ||       22 | 20         | 笔记本配件    | 索尼       |      99.000 |       1 |          0 |+----------+------------+---------------+------------+-------------+---------+------------+22 rows in set (0.00 sec)

goods_name 一栏已经被替换成占用数据更少的整数类型了，以后要查询商品名称可以通过子查询

举例：查询商品价格大于7000的商品名称

mysql> select goods_name from goods_name where name_id in (select goods_name from goods where goods_price>7000);+----------------------------------+| goods_name                       |+----------------------------------+| G150TH 15.6英寸游戏本            || SVP13226SCB 13.3英寸触控超极本   || iMac ME086CH/A 21.5英寸一体电脑  || Mac Pro MD878CH/A 专业级台式电脑 |+----------------------------------+4 rows in set (0.00 sec)

0 0