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

查看範例