将一个字段中的值拆分为两个字段

Question

我有一个表格字段membername，其中包含用户的名字和姓氏。是否可以将它们拆分成两个字段memberfirst和memberlast？

所有记录都具有此格式“名字 姓氏”（不带引号且中间有一个空格）。

- tsiger

---

所有记录的格式都是“名 姓”（不带引号且中间有一个空格）。令人惊奇的是，请，请在做数据库决策时不要忘记像我这样的人。太多时候，我会看到网站告诉我我的姓氏包含一个“非法”的字符……:( - Stijn de Witt

另请参见SQL将值拆分为多行。 - tripleee

Answer 1

不幸的是，MySQL没有拆分字符串的函数。但是你可以创建一个自定义函数来实现此功能，例如下面文章中描述的那个函数：

• MySQL Split String Function by Federico Cargnelutti

使用这个函数：

DELIMITER $$

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN 
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
END$$

DELIMITER ;

你可以按照以下方式构建你的查询：

SELECT SPLIT_STR(membername, ' ', 1) as memberfirst,
       SPLIT_STR(membername, ' ', 2) as memberlast
FROM   users;

如果您不想使用用户定义函数，并且您不介意查询语句有些冗长，您也可以执行以下操作：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 1), ' ', -1) as memberfirst,
       SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 2), ' ', -1) as memberlast
FROM   users;

- Daniel Vassallo

---

你仍然不能将IN作为从拆分操作中得到的“值数组”使用吗？ - Miguel

您对 LENGTH 函数的使用是否支持多字节字符安全性？“LENGTH(str)：返回字符串 str 的长度，以字节为单位。多字节字符会被计算为多个字节。这意味着，对于一个包含五个2字节字符的字符串，LENGTH() 返回10，而 CHAR_LENGTH() 返回5。” - Erk

这种方法在处理多字节/utf8字符时无法正常工作，正如@Erk所提到的那样。只有使用两个SUBSTRING_INDEX语句的简单解决方案才适用于utf8 / multibyte。 - Michael

LENGTH（），LOCATE（）或任何依赖于位置计数的函数在处理多字节字符时会出现错误。 - Michael

Answer 2

选择变体（不使用用户定义函数）：

SELECT IF(
        LOCATE(' ', `membername`) > 0,
        SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
        `membername`
    ) AS memberfirst,
    IF(
        LOCATE(' ', `membername`) > 0,
        SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
        NULL
    ) AS memberlast
FROM `user`;

这种方法还可以处理以下情况：

• 没有空格的 membername 值：它将整个字符串添加到 memberfirst 并将 memberlast 设置为 NULL。
• 有多个空格的 membername 值：它将第一个空格之前的所有内容添加到 memberfirst，将剩余的内容（包括额外的空格）添加到 memberlast。

UPDATE 版本如下：

UPDATE `user` SET
    `memberfirst` = IF(
        LOCATE(' ', `membername`) > 0,
        SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
        `membername`
    ),
    `memberlast` = IF(
        LOCATE(' ', `membername`) > 0,
        SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
        NULL
    );

- smhg

---

另外有用的是看看如何截取只有姓氏的最后一个单词，以及所有非姓氏的名字，例如：Mary A. Smith，在一个旧的数据库表格修复中我需要处理这种类型。如果我能想出结果，我会发帖分享，如果不能，请您提供这个选项，这将使您的答案更完整。 - Lizardx

我们如何将它转换为整数，因为membername是varchar类型。让memberfirst成为int类型。如果我直接使用cast()，它会起作用吗？ - infinitywarior

如何将memberlast中的最后一个单词作为memberlast字段，倒数第二个单词作为memberfirst字段？ - Fredrick M T Pardosi

Answer 3

现有的回答似乎过于复杂或者不能严格回答特定的问题。

我认为，简单的回答就是以下的查询:

SELECT
    SUBSTRING_INDEX(`membername`, ' ', 1) AS `memberfirst`,
    SUBSTRING_INDEX(`membername`, ' ', -1) AS `memberlast`
;

我认为在这种特定情况下，处理两个以上单词的名称并不是必要的。如果您想做到正确，拆分有时可能非常困难甚至不可能：

• Johann Sebastian Bach
• Johann Wolfgang von Goethe
• Edgar Allan Poe
• Jakob Ludwig Felix Mendelssohn-Bartholdy
• Petőfi Sándor
• Virág Vendelné Farkas Margit
• 黒澤 明

在设计良好的数据库中，人名应该同时以部分和整体的形式存储。当然，并非总是可行的。

- Dávid Horváth

---

如果您知道要期望多少个值，那就没问题了。但是，如果字段可能包含“one”、“one, two”或“one, two, three”等内容，并且您想将每个值拆分到单独的行中，则需要更复杂的操作。（虽然同意在设计良好的数据库中不应该出现这种情况，但您知道它确实存在。） - tripleee

@tripleee 一般来说是这样的，但现在我们处于一个更简单的情况下，因为OP说明了：“所有记录都具有此格式：“名字 姓氏”。 - Dávid Horváth

Answer 4

如果您计划将此作为查询的一部分来执行，请不要这样做^(a)。严肃地说，这会降低性能。也许有一些情况您并不在意性能（例如仅需进行一次性迁移作业以拆分字段，从而实现未来更好的性能），但是，如果您定期这样做，除了用于小型数据库之外，那么您就是在浪费资源。

如果您发现自己需要以某种方式仅处理列的一部分，那么您的数据库设计就有缺陷。它可能在家庭通讯录、食谱应用程序或其他无数小型数据库中运行良好，但不适用于“真正”的系统。

将名称组件存储在单独的列中。使用简单的连接操作（当您需要完整的名称时）几乎总是比使用字符搜索将它们拆分要快得多。

如果由于某种原因您无法拆分该字段，请至少添加额外的列并使用插入/更新触发器来填充它们。虽然不符合第三范式，但这将确保数据仍然一致，并显著加速您的查询。同时，您还可以确保这些额外列的小写形式（如果您在搜索它们，则进行索引），以便无需处理大小写问题。

而且，如果您甚至无法添加这些列和触发器，请注意（如果是为客户提供服务的话），这是不可扩展的。

---

^(a) 当然，如果您的意图是使用此查询在表中将名称放入单独的列中而不是查询中执行此操作，我认为这是一个有效的用途。但是，我再次强调，在查询中这样做并不是一个好主意。

- paxdiablo

---

有时候，你必须这么做。例如，我需要在迁移脚本中使用它，所以我不在意性能。 - Matthieu Napoli

@dfmiller，是的，我确实这样做了，因此我有理有据地回答了你，并感谢你的关注。如果你对我写的某些内容有具体问题，请指出来，我会看看是否可以改进。你目前的评论对改善情况几乎没有用处，如果这确实是你的意图。或者你只是喜欢在网络上胡言乱语，很难说 :-) 当然，我坚持我的答案，子列访问不可扩展，几乎总是一个坏主意，除非它被用于实际修复子列访问。 - paxdiablo

问题是如何将单列拆分为两个，你回答说“不要这样做”，然后解释为什么它们应该被拆分。你的第一段听起来像是支持保持它们作为一个列，但其他段落则表示相反的意见。 - dfmiller

@dfmiller，也许我误解了问题，现在我不确定分离是要在查询还是表中进行。我已经澄清了答案，希望能更清晰明了。 - paxdiablo

好多了。我从来没有考虑过使用选择查询，除非是为了更新数据库。那将是一个可怕的想法。 - dfmiller

非常好的答案！如果我要总结一下，就是：“如果你发现自己只需要以某种方式处理列的一部分，那么你的数据库设计是有缺陷的。”（尽管我继承了模式等，这就是生活；使用您的预填字段建议。） - HoldOffHunger

Answer 5

使用这个

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', 2 ),' ',1) AS b, 
SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', -1 ),' ',2) AS c FROM `users` WHERE `userid`='1'

- Karthik

---

这将从该字段中获取第一个和最后一个以空格分隔的子字符串，但并非在所有情况下都有效。例如，如果名称字段为“Lilly von Schtupp”，那么您将得到“Lilly”、“Schtupp”作为名字、姓氏。 - John Franklin

Answer 6

在MySQL中，这个选项是有效的：

SELECT Substring(nameandsurname, 1, Locate(' ', nameandsurname) - 1) AS 
       firstname, 
       Substring(nameandsurname, Locate(' ', nameandsurname) + 1)    AS lastname 
FROM   emp  

- Irakli Kardava

---

将剩余的字符串放在第二个字段中 - M. Faraz

Answer 7

虽然没有直接回答这个问题，但面对同样的问题，我最终做了这个：

UPDATE people_exit SET last_name = SUBSTRING_INDEX(fullname,' ',-1)
UPDATE people_exit SET middle_name = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fullname,last_name,1),' ',-2))
UPDATE people_exit SET middle_name = '' WHERE CHAR_LENGTH(middle_name)>3 
UPDATE people_exit SET first_name = SUBSTRING_INDEX(fullname,concat(middle_name,' ',last_name),1)
UPDATE people_exit SET first_name = middle_name WHERE first_name = ''
UPDATE people_exit SET middle_name = '' WHERE first_name = middle_name

- commonpike

Answer 8

我有一列数据，其中名字和姓氏都在同一个列中，用逗号隔开。下面的代码可以实现分离。这里没有进行任何错误检查/纠正，只是简单地使用了split函数。使用phpMyAdmin执行SQL语句。

UPDATE tblAuthorList SET AuthorFirst = SUBSTRING_INDEX(AuthorLast,',',-1) , AuthorLast = SUBSTRING_INDEX(AuthorLast,',',1);

13.2.10 UPDATE Syntax

- Steve R.

Answer 9

唯一需要使用这样一个函数的情况是 UPDATE 查询将更改您的表以将名字和姓氏存储到不同字段中。

数据库设计必须遵循某些规则，数据库规范化是其中最重要的规则之一。

- Your Common Sense

---

这是完全不必要的评论，因为有很多情况下需要拆分字符串以进行最佳规范化，此评论也不准确。我不确定为什么或如何它会被投票支持。 - daticon

在分割字段上使用索引就像把MySQL变成叶片粉碎机一样不可能，但这并不能阻止人们对此进行询问。好的答案是--数据库应该反映数据，而不是你的叶片粉碎机规格。 - HoldOffHunger

Answer 10

这将从这里获取smhg并从 MySQL中给定子字符串的最后一个索引中获取curt，并将它们组合起来。这是针对mysql的，我所需要的只是得到一个名字拆分为名和姓的合理方式，其中姓氏是一个单词，名字前面是所有单词，其中名字可以为空、1个单词、2个单词或多于2个单词。例如：Null；Mary；Mary Smith；Mary A. Smith；Mary Sue Ellen Smith；

因此，如果名字是一个单词或为空，则姓氏为空。如果名字有超过1个单词，则姓氏是最后一个单词，名字是最后一个单词之前的所有单词。

请注意，我已经手动修剪了Joe Smith Jr.；Joe Smith Esq.等内容，这当然很痛苦，但是它足够小，可以这样做，因此您需要确保在决定使用哪种方法之前仔细查看名称字段中的数据。

请注意，这也会修剪输出结果，因此您不会在姓名前面或后面得到空格。

我只是为了其他可能通过谷歌搜索而来的人所发表这篇文章，他们也需要我所需的内容。当然，首先要用选择进行测试。这只是一次性的事情，所以我不在意效率。

SELECT TRIM( 
    IF(
        LOCATE(' ', `name`) > 0,
        LEFT(`name`, LENGTH(`name`) - LOCATE(' ', REVERSE(`name`))),
        `name`
    ) 
) AS first_name,
TRIM( 
    IF(
        LOCATE(' ', `name`) > 0,
        SUBSTRING_INDEX(`name`, ' ', -1) ,
        NULL
    ) 
) AS last_name
FROM `users`;


UPDATE `users` SET
`first_name` = TRIM( 
    IF(
        LOCATE(' ', `name`) > 0,
        LEFT(`name`, LENGTH(`name`) - LOCATE(' ', REVERSE(`name`))),
        `name`
    ) 
),
`last_name` = TRIM( 
    IF(
        LOCATE(' ', `name`) > 0,
        SUBSTRING_INDEX(`name`, ' ', -1) ,
        NULL
    ) 
);

- Lizardx