将 MYSQL 列拆分为多个列

Question

我在mysql数据库中有几百万条记录，包含以下列：公司、地址、网址、电话、类别

以下是一行样例数据：Company123 - 123 Candyland St, New York, NY 12345 - http://urltothiscompany.com - 123-456-7890 - Bakery

我的问题是关于地址列。我想将这些行拆分成单独的地址、城市、州和邮政编码列：123 Candyland St - New York - NY - 12345

然而，有些行没有街道地址，只有城市、州和邮政编码：New York, NY, 1235

是否有可能在mysql中实现？由于某些行没有地址信息，所以我不确定从哪里开始。也许可以从该列末尾计算字符数吗？

非常感谢您的帮助。

- dkeeper09

---

我会使用逗号作为分隔符，并从字符串末尾开始倒数计数。第一部分将是邮政编码，接下来是州，城市和（如果存在）街道。阅读此内容：https://dev59.com/NXE85IYBdhLWcg3wmExW - CodeGodie

我的方法是编写一个PHP脚本，获取该列并在-上使用PHP的explode函数。您还可以使用isnumeric($zip)来确保它有效，或者至少不同于加拿大的美国。 - Duane Lortie

如果你确定只有街道可能缺失，那么CodeGodie提出的解决方案是正确的。但是，如果其他字段也可能缺失，我怀疑你需要某种验证... - Aerendir

在你的例子中：第一个没有逗号（... NY 12345），第二个有逗号（...，NY，1235），这对于数据切割来说很困难。 - SIDU

Answer 1

假设您的数据实际上如下所示：

addr
=======================
street, City, State ZIP

以下是SQL语句：

SELECT addr,
  substr(addr, 1, length(addr) - length(substring_index(addr, ',', -2))) street,
  substring_index(substring_index(addr, ',', -2), ',', 1) city,
  substr(trim(substring_index(addr, ',', -1)),1,2) state,
  substring_index(addr, ' ', -1) zip
FROM tab

这里有一个额外的逗号在“street”上，这是一个让你修复的作业 :)

- SIDU

---

抱歉回复晚了，非常好的答案！ - dkeeper09

我可以问一下这是什么GUI软件/IDE吗？ - flow2k

http://topnew.net/sidu - SIDU DB GUI适用于MySQL、PostgreSQL、SQLite等数据库。 - SIDU

Answer 2

给像我一样的谷歌用户快速回答。
例如将列分成3个。如果您只需要2个列，请在请求中省略new_column_name_2。
适用于类似100x200x300这样的尺寸，变成了100 200 300

1. 创建新列。

ALTER TABLE `your_table_name` ADD `new_column_name_1` VARCHAR(250) DEFAULT '';
ALTER TABLE `your_table_name` ADD `new_column_name_2` VARCHAR(250) DEFAULT '';
ALTER TABLE `your_table_name` ADD `new_column_name_3` VARCHAR(250) DEFAULT '';

2. 确认您选择的内容是否正确

SELECT `old_column_name`,
SUBSTRING_INDEX(`old_column_name`, 'x', 1) `new_column_name_1`,
SUBSTRING_INDEX(SUBSTRING_INDEX(`old_column_name`, 'x', 2),'x',-1) `new_column_name_2`, 
SUBSTRING_INDEX(`old_column_name`, 'x', -1) `new_column_name_3`
FROM `your_table_name`;

3. 填充新列

UPDATE `your_table_name` SET
`new_column_name_1` = SUBSTRING_INDEX(`old_column_name`, 'x', 1),
`new_column_name_2` = SUBSTRING_INDEX(SUBSTRING_INDEX(`old_column_name`, 'x', 2),'x',-1),
`new_column_name_3` = SUBSTRING_INDEX(`old_column_name`, 'x', -1);

- Hebe