mysql截取一列为两列

-- 创建示例表
CREATE TABLE users (
    id INT PRIMARY KEY,
    full_name VARCHAR(100)
);

-- 插入示例数据
INSERT INTO users (id, full_name) VALUES
(1, '张 三'),
(2, '李 四'),
(3, '王 五');

-- 使用SUBSTRING函数拆分full_name为first_name和last_name
ALTER TABLE users ADD COLUMN first_name VARCHAR(50);
ALTER TABLE users ADD COLUMN last_name VARCHAR(50);

UPDATE users
SET first_name = SUBSTRING_INDEX(full_name, ' ', 1),
    last_name = SUBSTRING_INDEX(full_name, ' ', -1);

UPDATE users
SET full_name = CONCAT(SUBSTRING_INDEX(full_name, ' ', 1), ' ', SUBSTRING_INDEX(full_name, ' ', -1))
WHERE full_name NOT REGEXP '^[^ ]+ [^ ]+$';

CREATE TEMPORARY TABLE temp_users AS
SELECT id,
       SUBSTRING_INDEX(full_name, ' ', 1) AS first_name,
       SUBSTRING_INDEX(full_name, ' ', -1) AS last_name
FROM users;

TRUNCATE TABLE users;

INSERT INTO users (id, first_name, last_name)
SELECT id, first_name, last_name FROM temp_users;

DROP TEMPORARY TABLE temp_users;