mysql - 删除每行 MySQL 的重复项

Question

我有一个看起来像这样的表

| Domain | Owner   | Nameservers         |
__________________________________________
| Test1  | Person1 | Serv1, Serv2, Serv3 |
| Test2  | Person2 | Serv1               |
| Test3  | Person3 | Serv1, Serv2        |

我的计划是将名称服务器拆分成自己的列。 (我需要这个结构来进一步导入第三方系统)..

| Domain | Owner   | Nameserver1 | Nameserver2 | Namerserver3 |
_______________________________________________________________
| Test1  | Person1 | Serv1       | Serv2       | Serv 3       |
| Test2  | Person2 | Serv1       |             |              |
| Test3  | Person3 | Serv1       | Serv2       |              |

表格中的空格可以是空字符串或NULL。

我试过以下查询:

CREATE TABLE temp_import_table AS
  SELECT domain, 
         owner, 
         SUBSTRING_INDEX( `nameservers` , ',', 1 ) AS nameserver1,
         SUBSTRING_INDEX(SUBSTRING_INDEX( `nameservers` , ',', 2 ),',',-1) AS nameserver2, 
         SUBSTRING_INDEX( `nameservers` , ',', -1 ) AS nameserver3

但不知何故我得到以下结果:

| Domain | Owner   | Nameserver1 | Nameserver2 | Namerserver3 |
_______________________________________________________________
| Test1  | Person1 | Serv1       | Serv2       | Serv3        |
| Test2  | Person2 | Serv1       | Serv1       | Serv1        |
| Test3  | Person3 | Serv1       | Serv2       | Serv2        |

如您所见，即使 csv 字段中的名称服务器为空，名称服务器也会填充“先前”的名称服务器。谁能帮我去掉/清空每行的重复值？

Answer 1

更好的解决方案是放弃这个插件，但只是为了好玩......

SELECT * FROM my_table;
+--------+---------+--------------+
| Domain | Owner   | Nameservers  |
+--------+---------+--------------+
| Test2  | Person2 | Serv1        |
| Test3  | Person3 | Serv1, Serv2 |
+--------+---------+--------------+

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT DISTINCT domain
              , owner
              , TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(nameservers,',',i+1),',',-1)) x 
           FROM my_table
              , ints;

+--------+---------+-------+
| domain | owner   | x     |
+--------+---------+-------+
| Test2  | Person2 | Serv1 |
| Test3  | Person3 | Serv1 |
| Test3  | Person3 | Serv2 |
+--------+---------+-------+