MySQL 将值从一个字段拆分为两个

Split value from one field to two

提问by tsiger

I've got a table field membernamewhich contains both the last name and the first name of users. Is it possible to split those into 2 fields memberfirst, memberlast?

我有一个表字段membername，其中包含用户的姓氏和名字。是否有可能分裂成那些2场memberfirst，memberlast？

All the records have this format "Firstname Lastname" (without quotes and a space in between).

所有记录都具有这种格式“名字姓氏”（没有引号和中间的空格）。

回答by Daniel Vassallo

Unfortunately MySQL does not feature a split string function. However you can create a user defined functionfor this, such as the one described in the following article:

不幸的是，MySQL 没有拆分字符串功能。但是，您可以为此创建一个用户定义的函数，例如以下文章中描述的函数：

• MySQL Split String Functionby Federico Cargnelutti

• 由 Federico Cargnelutti 编写的MySQL 拆分字符串函数

With that function:

有了这个功能：

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 ;

you would be able to build your query as follows:

您可以按如下方式构建查询：

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

If you prefer not to use a user defined function and you do not mind the query to be a bit more verbose, you can also do the following:

如果您不想使用用户定义的函数并且不介意查询更冗长，您还可以执行以下操作：

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

回答by smhg

SELECT variant (not creating a user defined function):

SELECT 变体（不创建用户定义的函数）：

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`;

This approach also takes care of:

这种方法还考虑到：

• membername values without a space: it will add the whole string to memberfirst and sets memberlast to NULL.
• membername values that have multiple spaces: it will add everything before the first space to memberfirst and the remainder (including additional spaces) to memberlast.

• 成员名称值没有空格：将整个字符串添加到memberfirst和套memberlast为NULL。
• 具有多个空格的membername值：它会将第一个空格之前的所有内容添加到 memberfirst 并将其余（包括附加空格）添加到 memberlast。

The UPDATE version would be:

更新版本将是：

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
    );

回答by Dávid Horváth

It seems that existing responses are over complicated or not a strict answer to the particular question.

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

I think, the simple answer is the following query:

我认为，简单的答案是以下查询：

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

---

---

I think it is not necessary to deal with more-than-two-word names in this particular situation. If you want to do it properly, splitting can be very hard or even impossible in some cases:

我认为在这种特殊情况下没有必要处理超过两个字的名字。如果你想正确地做到这一点，在某些情况下拆分可能非常困难甚至不可能：

• Johann Sebastian Bach
• Johann Wolfgang von Goethe
• Edgar Allan Poe
• Jakob Ludwig Felix Mendelssohn-Bartholdy
• Pet?fiSándor
• 黒澤明

• 约翰·塞巴斯蒂安·巴赫
• 约翰·沃尔夫冈·冯·歌德
• 埃德加·爱伦·坡
• 雅各布·路德维希·费利克斯·门德尔松-巴托迪
• Pet?fiSándor
• 泽黒：明

In a properly designed database, human names should be stored both in parts and in whole. This is not always possible, of course.

在设计合理的数据库中，人名应部分和全部存储。当然，这并不总是可能的。

回答by paxdiablo

If your plan is to do this as part of a query,please don'tdo that ^(a). Seriously, it's a performance killer. There may be situations where you don't care about performance (such as one-off migration jobs to split the fields allowing better performance in future) but, if you're doing this regularly for anything other than a mickey-mouse database, you're wasting resources.

如果您计划将此作为查询的一部分，请不要这样做^(a)。说真的，这是一个性能杀手。在某些情况下，您可能不关心性能（例如一次性迁移作业以拆分字段以在将来获得更好的性能）但是，如果您定期为 mickey-mouse 数据库以外的任何东西执行此操作，您正在浪费资源。

If you everfind yourself having to process only part of a column in some way, your DB design is flawed. It may well work okay on a home address book or recipe application or any of myriad other small databases but it will not be scalable to "real" systems.

如果你曾经发现自己有只处理以某种方式一栏的一部分，你的数据库的设计是有缺陷的。它可能在家庭地址簿或食谱应用程序或无数其他小型数据库中的任何一个上都可以正常工作，但它无法扩展到“真实”系统。

Store the components of the name in separate columns. It's almost invariably a lot faster to join columns together with a simple concatenation (when you need the full name) than it is to split them apart with a character search.

将名称的组成部分存储在单独的列中。使用简单的连接（当您需要全名时）将列连接在一起几乎总是比使用字符搜索将它们分开要快得多。

If, for some reason you cannot split the field, at least put in the extra columns and use an insert/update trigger to populate them. While not 3NF, this will guarantee that the data is still consistent and will massively speed up your queries. You could also ensure that the extra columns are lower-cased (and indexed if you're searching on them) at the same time so as to not have to fiddle around with case issues.

如果由于某种原因您无法拆分字段，请至少放入额外的列并使用插入/更新触发器来填充它们。虽然不是 3NF，但这将保证数据仍然一致，并将大大加快您的查询速度。您还可以同时确保额外的列是小写的（如果您正在搜索它们，并且索引），以便不必摆弄大小写问题。

And, if you cannot even add the columns and triggers, be aware (and make your client aware, if it's for a client) that it is not scalable.

而且，如果您甚至无法添加列和触发器，请注意（并让您的客户知道，如果是针对客户的）它是不可扩展的。

---

---

^(a)Of course, if your intent is to use this query to fixthe schema so that the names are placed into separate columns in the tablerather than the query,I'd consider that to be a valid use. But I reiterate, doing it in the query is not really a good idea.

^（一）当然，如果你的目的是使用此查询到固定的模式，这样的名字被放入单独的列在表中，而不是查询，我认为这是一个有效的使用。但我重申，在查询中这样做并不是一个好主意。

回答by Karthik

use this

用这个

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

回答by commonpike

Not exactly answering the question, but faced with the same problem I ended up doing this:

没有完全回答这个问题，但面对同样的问题，我最终这样做了：

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

回答by Irakli Kardava

In MySQL this is working this option:

在 MySQL 中，这是使用此选项：

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

回答by Your Common Sense

The only case where you may want such a function is an UPDATE query which will alter your table to store Firstname and Lastname into separate fields.

您可能需要这样一个函数的唯一情况是 UPDATE 查询，它会改变您的表以将 Firstname 和 Lastname 存储到单独的字段中。

Database design must follow certain rules, and Database Normalizationis among most important ones

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

回答by Steve R.

I had a column where the first and last name were both were in one column. The first and last name were separated by a comma. The code below worked. There is NO error checking/correction. Just a dumb split. Used phpMyAdmin to execute the SQL statement.

我有一个列，其中名字和姓氏都在一个列中。名字和姓氏用逗号分隔。下面的代码有效。没有错误检查/纠正。只是一个愚蠢的分裂。使用 phpMyAdmin 执行 SQL 语句。

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

13.2.10 UPDATE Syntax

13.2.10 更新语法

回答by Lizardx

This takes smhg from here and curt's from Last index of a given substring in MySQLand combines them. This is for mysql, all I needed was to get a decent split of name to first_name last_name with the last name a single word, the first name everything before that single word, where the name could be null, 1 word, 2 words, or more than 2 words. Ie: Null; Mary; Mary Smith; Mary A. Smith; Mary Sue Ellen Smith;

这从这里获取 smhg 并从MySQL 中给定子字符串的最后一个索引获取 curt并将它们组合起来。这是针对 mysql 的，我所需要的只是将名字适当地拆分为 first_name last_name，其中姓氏是一个单词，名字是该单词之前的所有内容，其中名称可以是空值、1 个单词、2 个单词或超过2个字。即：空；玛丽; 玛丽·史密斯；玛丽·A·史密斯；玛丽苏艾伦史密斯；

So if name is one word or null, last_name is null. If name is > 1 word, last_name is last word, and first_name all words before last word.

因此，如果 name 是一个单词或为 null，则 last_name 为 null。如果 name > 1 个单词，last_name 是最后一个单词，first_name 是最后一个单词之前的所有单词。

Note that I've already trimmed off stuff like Joe Smith Jr. ; Joe Smith Esq. and so on, manually, which was painful, of course, but it was small enough to do that, so you want to make sure to really look at the data in the name field before deciding which method to use.

请注意，我已经删除了 Joe Smith Jr. 之类的东西；乔·史密斯 Esq. 等等，手动，这当然很痛苦，但它足够小，可以做到这一点，所以在决定使用哪种方法之前，您要确保真正查看 name 字段中的数据。

Note that this also trims the outcome, so you don't end up with spaces in front of or after the names.

请注意，这也会修剪结果，因此您不会在名称之前或之后出现空格。

I'm just posting this for others who might google their way here looking for what I needed. This works, of course, test it with the select first.

我只是为其他可能在这里用谷歌搜索我需要的东西的人发布这个。当然，这是有效的，首先使用 select 对其进行测试。

It's a one time thing, so I don't care about efficiency.

这是一次性的，所以我不在乎效率。

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
    ) 
);