MySQL字符串分割-进城务工人员小梅

在MySQL中没有字符串分割函数功能，但可以通过巧妙地使用不同的字符串函数来实现。假设有如下的表：

CREATE TABLE `address` (
`id` INTEGER AUTO_INCREMENT PRIMARY KEY,
`fullname` VARCHAR(255),
`company` VARCHAR(255),
`street` VARCHAR(255),
`city` VARCHAR(255),
`state` VARCHAR(255),
`zip` VARCHAR(20),
`country` VARCHAR(255)
);

CREATE TABLE `address` (

`id` INTEGER AUTO_INCREMENT PRIMARY KEY,

`fullname` VARCHAR(255),

`company` VARCHAR(255),

`street` VARCHAR(255),

`city` VARCHAR(255),

`state` VARCHAR(255),

`zip` VARCHAR(20),

`country` VARCHAR(255)

);

和数据：

INSERT INTO `address` VALUES (
NULL,
'Test Testerson',
'ACME, Inc',
'123 Acme Way\nSuite 200',
'San Francisco',
'California',
'94114',
'United States'
);

INSERT INTO `address` VALUES (

NULL,

'Test Testerson',

'ACME, Inc',

'123 Acme Way\nSuite 200',

'San Francisco',

'California',

'94114',

'United States'

);

要分割的字符串

假设我们要查询此表并将街道信息拆分为多列，那么可以使用MySQL中的SUBSTRING_INDEX函数：

SELECT SUBSTRING_INDEX(street, '\n', 1) AS street1, SUBSTRING_INDEX(street, '\n', 2) AS street2, SUBSTRING_INDEX(street, '\n', 3) AS street3 FROM address;

1	SELECT SUBSTRING_INDEX(street, '\n', 1) AS street1, SUBSTRING_INDEX(street, '\n', 2) AS street2, SUBSTRING_INDEX(street, '\n', 3) AS street3 FROM address;

然而，这种结果是有问题的：

MySQL中错误的字符串分割

显然，street2和street3的分割结果是错误的。如果我们仔细阅读MySQL文档，那么会注意到SUBSTRING_INDEX返回的是分隔符左侧的所有内容。如果我们想要获取分隔符第一次和第二次出现之间的字符串，那么可以通过将street2和street3包装在另一个SUBSTRING_INDEX中来进行：

SELECT
SUBSTRING_INDEX(street, '\n', 1) AS street1,
SUBSTRING_INDEX(SUBSTRING_INDEX(street, '\n', 2), '\n', -1) AS street2,
SUBSTRING_INDEX(SUBSTRING_INDEX(street, '\n', 3), '\n', -1) AS street3
FROM address;

SELECT

SUBSTRING_INDEX(street, '\n', 1) AS street1,

SUBSTRING_INDEX(SUBSTRING_INDEX(street, '\n', 2), '\n', -1) AS street2,

SUBSTRING_INDEX(SUBSTRING_INDEX(street, '\n', 3), '\n', -1) AS street3

FROM address;

这样就基本上差不多似乎正确的了。但是，street3返回的值与street2仍然是不对的：

MySQL中错误的字符串分割2

这是因为这一条个街道信息只有两行，如果要正确处理那么就需要使用IF语句来控制SUBSTRING_INDEX：

SELECT
@num_street_lines := 1 + LENGTH(street) - LENGTH(REPLACE(street, '\n', '')) AS num_street_lines,
SUBSTRING_INDEX(street, '\n', 1) AS street1,
IF(@num_street_lines > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(street, '\n', 2), '\n', -1), '') AS street2,
IF(@num_street_lines > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(street, '\n', 3), '\n', -1), '') AS street3
FROM address;

SELECT