问题及解答

现在在数据库 test 下有一张表 city. 内容如下

现在要求将该表重新设计, 模式为 city2(name, country), 并且将数据自动存储到新表 city2 中.

假设 city 表中的数据非常多, 只能使用 MySQL 自带的工具.

[Hint]

使用 MySQL 自带的函数 SUBSTRING_INDEX.

mysql> SELECT SUBSTRING_INDEX(CityName,',',1) FROM city;
+---------------------------------+
| SUBSTRING_INDEX(CityName,',',1) |
+---------------------------------+
| Hyderabad                       |
| San Francisco                   |
| Sydney                          |
+---------------------------------+
3 rows in set (0.00 sec)

将查询所得的临时关系插入到另一张表中.

mysql> insert into city2(name) select SUBSTRING_INDEX(CityName,',',1) from city;
Query OK, 3 rows affected (0.01 sec)
Enregistrements: 3  Doublons: 0  Avertissements: 0

mysql> select * from city2;
+---------------+---------+
| name          | country |
+---------------+---------+
| Hyderabad     | NULL    |
| San Francisco | NULL    |
| Sydney        | NULL    |
+---------------+---------+
3 rows in set (0.00 sec)

但是这仅仅插入了城市名.

如果继续执行下面的语句

mysql> insert into city2(country) select SUBSTRING_INDEX(CityName,',',-1) from city;
Query OK, 3 rows affected (0.04 sec)
Enregistrements: 3  Doublons: 0  Avertissements: 0

则得到的并非我们想要的结果.

mysql> select * from city2;
+---------------+------------+
| name          | country    |
+---------------+------------+
| Hyderabad     | NULL       |
| San Francisco | NULL       |
| Sydney        | NULL       |
| NULL          |  India     |
| NULL          |  USA       |
| NULL          |  Australia |
+---------------+------------+
6 rows in set (0.00 sec)

现在将上面的语句改动一下.

insert into city2(name, country) select SUBSTRING_INDEX(CityName,',',1), SUBSTRING_INDEX(CityName,',',-1) from city;

mysql> truncate table city2;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from city2;
Empty set (0.00 sec)

mysql> insert into city2(name, country) select SUBSTRING_INDEX(CityName,',',1), SUBSTRING_INDEX(CityName,',',-1) from city;
Query OK, 3 rows affected (0.04 sec)
Enregistrements: 3  Doublons: 0  Avertissements: 0

mysql> select * from city2;
+---------------+------------+
| name          | country    |
+---------------+------------+
| Hyderabad     |  India     |
| San Francisco |  USA       |
| Sydney        |  Australia |
+---------------+------------+
3 rows in set (0.00 sec)

需要注意的是, 分隔后的字符串, 可能两端有空格. 比如上面的 'USA', 前面还有一个空格.

MySQL 提供了 trim() 函数可以将字符串的前导和尾部空格去除.

mysql> UPDATE city2 SET name=TRIM(name), country=TRIM(country);
Query OK, 3 rows affected (0.04 sec)
Enregistrements correspondants: 3  Modifi茅s: 3  Warnings: 0

mysql> SELECT * FROM city2;
+---------------+-----------+
| name          | country   |
+---------------+-----------+
| Hyderabad     | India     |
| San Francisco | USA       |
| Sydney        | Australia |
+---------------+-----------+
3 rows in set (0.00 sec)