MySQL，使用fullname列中的数据添加列'FullNameReverseOrder'，并将（FName，LName）更改为（LName，FName）

Question

我想在一个名为NameTable的表中添加一个名为FullNameReverseOrder的新列，其中FullName的信息按照FirstName LastName的顺序排列，而FullNameReverseOrder将按照LastName FirstName的顺序保存。

这是一个你可以使用的表：

create table NameTable (ID int, FullName varchar(100), age int, primary key(ID));
insert into NameTable (ID, FullName, age) values(1, 'ben thompson', 23);

添加名为FullNameReverseOrder的新列：
alter table NameTable add column FullNameReverseOrder varchar(100) ...不知道在这里该怎么做... after FullName;

Answer 1

需要考虑的一些要点。

• 不要存储年龄，因为每年都会变化，表格需要更新，改用出生日期。
• 如果有的话，使用单独的列来存储名字、姓氏和中间名。

根据问题

考虑以下数据示例，其中FullName列最多由三个用空格分隔的单词组成

create table NameTable (
  ID int, 
  FullName varchar(100), 
  age int, 
  primary key(ID) );

insert into NameTable (ID, FullName, age) values
  (1, 'ben thompson', 23),
  (2, 'Martin Luther King', 23);

查询，

SELECT SUBSTRING_INDEX(TRIM(FullName), ' ', -1) LastName,
       SUBSTRING_INDEX(TRIM(FullName), ' ', 1) FirstName,
       SUBSTR(FullName, LOCATE(' ',FullName) + 1,  (CHAR_LENGTH(FullName) - LOCATE(' ',REVERSE(FullName)) - LOCATE(' ',FullName)))  AS MiddleName   
FROM NameTable;

结果，

LastName    FirstName   MiddleName
Thompson      Ben   
King          Martin      Luther

首先通过修改表结构来进行更改，如果有大量的事务，我建议适当锁定表

SET autocommit=0; 
LOCK TABLES NameTable WRITE; 
alter table NameTable add column FullNameReverseOrder varchar(100) after FullName;
COMMIT; 
UNLOCK TABLES;

要更新新添加的列LastName，MiddleName和FirstName，请使用以下命令：

update NameTable
set FullNameReverseOrder = concat_ws(' ' ,SUBSTRING_INDEX(TRIM(FullName), ' ', -1),
                                  SUBSTR(FullName, LOCATE(' ',FullName)+1,  (CHAR_LENGTH(FullName) - LOCATE(' ',REVERSE(FullName)) - LOCATE(' ',FullName))),
                                  SUBSTRING_INDEX(TRIM(FullName), ' ', 1) );

选择，

select * 
from NameTable;

结果

ID  FullName             FullNameReverseOrder      age
1   ben thompson          thompson  ben            23
2   Martin Luther King    King Luther Martin       23

现在，如果您希望该过程自动进行，请考虑创建一个触发器。

CREATE TRIGGER FullNameReverseOrderUpdate BEFORE INSERT ON NameTable
FOR EACH ROW BEGIN

SET new.FullNameReverseOrder = (concat_ws(' ' ,SUBSTRING_INDEX(TRIM(new.FullName), ' ', -1),
                                  SUBSTR(new.FullName, LOCATE(' ',new.FullName)+1,  (CHAR_LENGTH(new.FullName) - LOCATE(' ',REVERSE(new.FullName)) - LOCATE(' ',new.FullName))),SUBSTRING_INDEX(TRIM(new.FullName), ' ', 1) ));
END;

插入测试值

insert into NameTable (ID, FullName, age) values
  (3, 'Arthur  Thompson', 23);

select * from NameTable;

结果

ID  FullName             FullNameReverseOrder     age
1   ben thompson           thompson  ben           23
2   Martin Luther King     King Luther Martin      23
3   Arthur  Thompson       Thompson  Arthur        23

查看示例