【金仓数据库产品体验官】_从实践看金仓数据库与 MySQL 的兼容性
为了验证金仓数据库（MySQL兼容版）和 MySQL 数据库在以下 DML 操作 和 数据查询 语句的兼容性，我通过以下测试来确保两者的兼容性：
- DML 操作：包括
INSERT IGNORE、LIMIT子句、INSERT ON DUPLICATE KEY、REPLACE INTO和LOAD DATA INFILE。 - 数据查询语句：包括
GROUP BY WITH ROLLUP来生成多级汇总行。
1. 测试思路
- 执行相同的 DML 操作：
- 确保在 MySQL 和金仓数据库中执行相同的 DML 操作（例如
INSERT IGNORE、LIMIT、ON DUPLICATE KEY、REPLACE INTO等），检查结果是否一致。
- 确保在 MySQL 和金仓数据库中执行相同的 DML 操作（例如
- 验证 LOAD DATA INFILE：
- 检查两个数据库是否能够正确加载文件，并且数据在表中的插入顺序和内容一致。
- 执行数据查询：
- 执行
GROUP BY WITH ROLLUP查询，确保在两个数据库中返回的结果一致，并且汇总行的格式相同。
- 执行
测试环境：
mysql版本：8.0.12
kingbase版本： V009R003C011
2. 数据操作语句兼容性测试（DML操作）
2.1 INSERT IGNORE语句
该语句用于插入数据，如果存在重复的唯一键，则忽略该操作。
-- 在 MySQL 和金仓数据库中执行相同的 `INSERT IGNORE` 语句
INSERT IGNORE INTO employees (employee_id, name, department_id, hire_date, salary, job_title, email, phone_number)
VALUES (1, 'John Doe', 1, '2020-01-15', 5000.00, 'HR Manager', 'john.doe@example.com', '123-456-7890');- 这个测试确保在两者数据库中，当插入重复数据时，不会插入重复记录，而是忽略该插入。
kingbase执行结果：
--金仓
INSERT IGNORE INTO employees (employee_id, name, department_id, hire_date, salary, job_title, email, phone_number)
VALUES (1, 'John Doe', 1, '2020-01-15', 5000.00, 'HR Manager', 'john.doe@example.com', '123-456-7890')
> WARNING: duplicate key value violates unique constraint "employees_pkey"
DETAIL: Key (employee_id)=(1) already exists.
> Affected rows: 0
> 查询时间: 0.165s
mysql执行结果：
--mysql
INSERT IGNORE INTO employees (employee_id, name, department_id, hire_date, salary, job_title, email, phone_number)
VALUES (1, 'John Doe', 1, '2020-01-15', 5000.00, 'HR Manager', 'john.doe@example.com', '123-456-7890')
> Affected rows: 0
> 查询时间: 0.161s
2.2 LIMIT 子句
用于限制返回的记录数，通常在查询中使用，但在 UPDATE 和 DELETE 操作中也可以使用。
-- 在 MySQL 和金仓数据库中执行 `LIMIT` 子句的 `UPDATE` 语句
UPDATE employees
SET salary = 6000.00
WHERE department_id = 1
LIMIT 2;- 这个测试验证是否能够正确地更新部分记录（通过
LIMIT来限制更新的记录数）。
kingbase执行结果：
UPDATE employees
SET salary = 6000.00
WHERE department_id = 1
LIMIT 2
> Affected rows: 0
> 查询时间: 0.043s
mysql执行结果：
UPDATE employees
SET salary = 6000.00
WHERE department_id = 1
LIMIT 2
> Affected rows: 0
> 查询时间: 0.040s
2.3 INSERT ON DUPLICATE KEY UPDATE 语句
如果插入的数据的唯一键已经存在，则执行更新操作。
-- 在 MySQL 和金仓数据库中执行 `INSERT ON DUPLICATE KEY UPDATE` 语句
INSERT INTO employees (employee_id, name, department_id, hire_date, salary, job_title, email, phone_number)
VALUES (1, 'John Doe', 1, '2020-01-15', 6000.00, 'HR Manager', 'john.doe@example.com', '123-456-7890')
ON DUPLICATE KEY UPDATE salary = 6000.00;- 这个测试确保在插入时，如果记录已经存在，会执行更新操作。
kingbase执行结果：
INSERT INTO employees (employee_id, name, department_id, hire_date, salary, job_title, email, phone_number)
VALUES (1, 'John Doe', 1, '2020-01-15', 6000.00, 'HR Manager', 'john.doe@example.com', '123-456-7890')
ON DUPLICATE KEY UPDATE salary = 6000.00
> Affected rows: 1
> 查询时间: 0.046s
mysql执行结果：
INSERT INTO employees (employee_id, name, department_id, hire_date, salary, job_title, email, phone_number)
VALUES (1, 'John Doe', 1, '2020-01-15', 6000.00, 'HR Manager', 'john.doe@example.com', '123-456-7890')
ON DUPLICATE KEY UPDATE salary = 6000.00
> Affected rows: 0
> 查询时间: 0.029s
2.4 REPLACE INTO 语句
如果插入的数据的唯一键已经存在，则删除现有记录并插入新记录。
-- 在 MySQL 和金仓数据库中执行 `REPLACE INTO` 语句
REPLACE INTO employees (employee_id, name, department_id, hire_date, salary, job_title, email, phone_number)
VALUES (1, 'John Doe', 1, '2020-01-15', 6000.00, 'HR Manager', 'john.doe@example.com', '123-456-7890');- 这个测试确保
REPLACE INTO能够正常执行，即如果记录存在，则先删除再插入新的记录。
kingbase执行结果：
REPLACE INTO employees (employee_id, name, department_id, hire_date, salary, job_title, email, phone_number)
VALUES (1, 'John Doe', 1, '2020-01-15', 6000.00, 'HR Manager', 'john.doe@example.com', '123-456-7890')
> ERROR: insert or update on table "employees" violates foreign key constraint "employees_department_id_fkey"
DETAIL: Key (department_id)=(1) is not present in table "departments".> 查询时间: 0.047s--需要先关掉约束
SET foreign_key_checks = 0;--重新执行
REPLACE INTO employees (employee_id, name, department_id, hire_date, salary, job_title, email, phone_number)
VALUES (1, 'John Doe', 1, '2020-01-15', 6000.00, 'HR Manager', 'john.doe@example.com', '123-456-7890')
> Affected rows: 2
> 查询时间: 0.137s--打开外键约束
SET foreign_key_checks = 1;
mysql执行结果：
REPLACE INTO employees (employee_id, name, department_id, hire_date, salary, job_title, email, phone_number)
VALUES (1, 'John Doe', 1, '2020-01-15', 6000.00, 'HR Manager', 'john.doe@example.com', '123-456-7890')
> 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`KKCP`.`employee_projects`, CONSTRAINT `employee_projects_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`))
> 查询时间: 0.068s--需要先关掉约束
SET GLOBAL FOREIGN_KEY_CHECKS = 0;--重新执行
REPLACE INTO employees (employee_id, name, department_id, hire_date, salary, job_title, email, phone_number)
VALUES (1, 'John Doe', 1, '2020-01-15', 6000.00, 'HR Manager', 'john.doe@example.com', '123-456-7890')
> Affected rows: 2
> 查询时间: 0.032s--打开外键约束
SET GLOBAL FOREIGN_KEY_CHECKS = 1;
2.5 LOAD DATA INFILE 语句
将数据从文件中加载到表中。
-- 在 MySQL 和金仓数据库中执行 `LOAD DATA INFILE` 语句
LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(employee_id, name, department_id, hire_date, salary, job_title, email, phone_number);- 这个测试确保从文件加载数据时，字段和行分隔符设置正确，且能够正常加载数据。
kingbase执行结果：
-- 在 MySQL 和金仓数据库中执行 `LOAD DATA INFILE` 语句LOAD DATA INFILE '/home/kingbase/file.csv' INTO TABLE employees FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
--返回结果
> Affected rows: 1
> 查询时间: 0.100s--验证查询
test=# select * from mysql.employees WHERE employee_id=49;employee_id | name | department_id | hire_date | salary | job_title | email | phone_number
-------------+--------+---------------+------------+----------+-----------+------------------+--------------49 | 金小仓 | 187 | 2025-08-08 | 12000.00 | 测试专员 | test@hotmail.com | 13217166544 +| | | | | | |
(1 row)
参考文档：https://bbs.kingbase.com.cn/kingbase-doc/v9.3.11/development/sql-plsql/sql/sql_statements/database_administration.html#load-data-infile
注意事项：这边的语法跟mysql略有不同，不能再命令里写具体的字段，直接load data
mysql执行结果：
--mysql
mysql> LOAD DATA INFILE '/data/mysql/files/file.csv' INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(employee_id, name, department_id, hire_date, salary, job_title, email, phone_number);
--返回结果
Query OK, 1 row affected (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0--验证查询
mysql> select * from employees WHERE employee_id=49;
+-------------+-----------+---------------+------------+----------+--------------+------------------+--------------+
| employee_id | name | department_id | hire_date | salary | job_title | email | phone_number |
+-------------+-----------+---------------+------------+----------+--------------+------------------+--------------+| 49 | 金小仓 | 187 | 2025-08-08 | 12000.00 | 测试专员 | test@hotmail.com | 13217166544
+-------------+-----------+---------------+------------+----------+--------------+------------------+--------------+
1 row in set (0.00 sec)
3. 数据查询语句兼容性测试
3.1 GROUP BY WITH ROLLUP 语句
用于生成多级汇总行，在 GROUP BY 查询的基础上生成汇总行。
-- 在 MySQL 和金仓数据库中执行 `GROUP BY WITH ROLLUP` 语句
SELECT department_id, COUNT(*) AS employee_count, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id WITH ROLLUP;- 这个测试确保在执行
GROUP BY WITH ROLLUP时，能够正确生成多级汇总行，包含所有部门以及整体的汇总数据。
kingbase执行结果：
SELECT department_id, COUNT(*) AS employee_count, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id WITH ROLLUP
> OK
> 查询时间: 0.047smysql执行结果：
SELECT department_id, COUNT(*) AS employee_count, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id WITH ROLLUP
> OK
> 查询时间: 0.069s
总结
通过本次测试，我们发现金仓数据库与 MySQL 在基本的 DML 操作和数据查询语句上表现出较高的兼容性。虽然存在一些细微差异，特别是在外键约束处理和语法细节上，整体而言，金仓数据库能够有效支持 MySQL 的大多数常见操作。
为确保兼容性和稳定性，建议在实际应用中继续关注两者在特定业务场景下的表现，尤其是在复杂的数据操作和查询中可能出现的性能差异。同时，针对金仓数据库的特有功能和约束，用户可根据具体需求进行适当配置优化。
附录
金仓数据库静默安装方式：
--创建软件安装目录
mkdir -p /KingbaseES/V9
mkdir -p /data
mkdir -p /backupchown -R kingbase:kingbase /KingbaseES/V9
chown -R kingbase:kingbase /data
chown -R kingbase:kingbase /backup
chown -R kingbase:kingbase /installchmod -R 755 /KingbaseES/V9
chmod -R 755 /backup
chmod -R 700 /data
chmod -R 775 /install--复制静默安装脚本silent.cfg参考如下：
cd /mnt/setup/
cp silent.cfg /home/kingbase/silent.cfg
chmod 775 silent.cfg -- vi /home/kingbase/silent.cfg
KB_LICENSE_PATH=/install/license_34148/license_34148_0.dat
USER_INSTALL_DIR=/KingbaseES/V9
USER_SELECTED_DATA_FOLDER=/data
##其它参数自己修改--执行安装
[kingbase@node ~]$ bash /mnt/setup.sh -i silent -f /home/kingbase/silent.cfg
Now launch installer....Complete.[root@node ~]# /KingbaseES/V9/install/script/root.sh
Starting KingbaseES V9:
waiting for server to start..... done
server started
KingbaseES V9 started successfully--让环境变量生效
vi .bash_profile
export KINGBASE_HOME=/KingbaseES/V9/Server
export KINGBASE_DATA=/data
export KINGBASE_PORT=54321
export PATH=$PATH:$KINGBASE_HOME/bin--使用 root 用户执行服务注册脚本
/KingbaseES/V9/install/script/root.sh
重启
reboot
测试数据：
-- 创建员工表 (employees)
CREATE TABLE employees (employee_id INT PRIMARY KEY, -- 员工ID，主键name VARCHAR(100) NOT NULL, -- 员工姓名，不能为空department_id INT, -- 部门ID，外键hire_date DATE, -- 入职日期salary DECIMAL(10, 2), -- 工资，保留两位小数job_title VARCHAR(50), -- 工作职位email VARCHAR(100), -- 电子邮件phone_number VARCHAR(20), -- 电话号码FOREIGN KEY (department_id) REFERENCES departments(department_id) -- 外键约束
);-- 创建部门表 (departments)
CREATE TABLE departments (department_id INT PRIMARY KEY, -- 部门ID，主键name VARCHAR(50) NOT NULL, -- 部门名称，不能为空location VARCHAR(100) -- 部门所在位置
);-- 创建项目表 (projects)
CREATE TABLE projects (project_id INT PRIMARY KEY, -- 项目ID，主键project_name VARCHAR(100), -- 项目名称start_date DATE, -- 项目开始日期end_date DATE, -- 项目结束日期budget DECIMAL(15, 2) -- 项目预算
);-- 创建员工与项目关联表 (employee_projects)
CREATE TABLE employee_projects (employee_id INT, -- 员工ID，外键project_id INT, -- 项目ID，外键role VARCHAR(50), -- 员工在项目中的角色hours_worked DECIMAL(5, 2), -- 工时PRIMARY KEY (employee_id, project_id), -- 联合主键FOREIGN KEY (employee_id) REFERENCES employees(employee_id), -- 外键约束FOREIGN KEY (project_id) REFERENCES projects(project_id) -- 外键约束
);-- 创建员工工资历史表 (salary_history)
CREATE TABLE salary_history (history_id INT PRIMARY KEY, -- 历史记录ID，主键employee_id INT, -- 员工ID，外键salary DECIMAL(10, 2), -- 工资start_date DATE, -- 起始日期end_date DATE, -- 结束日期FOREIGN KEY (employee_id) REFERENCES employees(employee_id) -- 外键约束
);SET foreign_key_checks = 1;
truncate table employees;
truncate table departments;
truncate table projects;
truncate table employee_projects;
truncate table salary_history;drop TABLE employees;
drop TABLE departments;
drop TABLE projects;
drop TABLE employee_projects;
drop TABLE salary_history;-- 查询员工表
SELECT * FROM employees;-- 查询部门表
SELECT * FROM departments;-- 查询项目表
SELECT * FROM projects;-- 查询员工与项目关联表
SELECT * FROM employee_projects;-- 查询员工工资历史表
SELECT * FROM salary_history;-- 插入部门数据 (departments)
INSERT INTO departments (department_id, name, location) VALUES
(101, '研发部', '上海'),
(102, '市场部', '北京'),
(103, '销售部', '广州'),
(104, '人力资源部', '上海'),
(105, '财务部', '上海'),
(106, '运营部', '深圳'),
(107, '法务部', '上海'),
(108, '客户服务部', '武汉'),
(109, '产品部', '杭州'),
(110, '行政部', '上海'),
(111, '技术支持部', '成都'),
(112, '供应链部', '苏州'),
(113, '质量控制部', '重庆'),
(114, '公共关系部', '北京'),
(115, '数据分析部', '上海');-- 插入员工数据 (employees)
INSERT INTO employees (employee_id, name, department_id, hire_date, salary, job_title, email, phone_number) VALUES
(1, '张三', 101, '2020-01-15', 8000.00, '软件工程师', 'zhangsan@example.com', '13812345678'),
(2, '李四', 101, '2019-03-20', 9500.00, '高级软件工程师', 'lisi@example.com', '13987654321'),
(3, '王五', 102, '2021-06-01', 7000.00, '市场专员', 'wangwu@example.com', '13700112233'),
(4, '赵六', 103, '2018-11-10', 12000.00, '销售经理', 'zhaoliu@example.com', '13611223344'),
(5, '钱七', 104, '2022-02-28', 6500.00, 'HR专员', 'qianqi@example.com', '13555667788'),
(6, '孙八', 101, '2020-09-01', 8800.00, '测试工程师', 'sunba@example.com', '13344556677'),
(7, '周九', 102, '2023-01-05', 7200.00, '市场经理', 'zhoujiu@example.com', '13277889900'),
(8, '吴十', 105, '2019-07-12', 10000.00, '财务主管', 'wushi@example.com', '13122334455'),
(9, '郑十一', 101, '2021-04-01', 8200.00, '前端开发', 'zheng11@example.com', '13012345670'),
(10, '冯十二', 103, '2022-08-10', 9000.00, '销售代表', 'feng12@example.com', '13109876543'),
(11, '陈十三', 106, '2023-03-15', 7800.00, '运营专员', 'chen13@example.com', '13223456789'),
(12, '褚十四', 107, '2022-01-20', 11000.00, '法务顾问', 'chu14@example.com', '13334567890'),
(13, '卫十五', 101, '2021-11-01', 9200.00, '全栈工程师', 'wei15@example.com', '13445678901'),
(14, '蒋十六', 104, '2023-06-01', 6800.00, '招聘专员', 'jiang16@example.com', '13556789012'),
(15, '沈十七', 105, '2020-05-01', 9800.00, '会计', 'shen17@example.com', '13667890123'),
(16, '韩十八', 101, '2022-07-01', 8500.00, '数据分析师', 'han18@example.com', '13778901234'),
(17, '杨十九', 102, '2021-09-10', 7500.00, '品牌专员', 'yang19@example.com', '13889012345'),
(18, '朱二十', 103, '2023-02-01', 8800.00, '销售代表', 'zhu20@example.com', '13990123456'),
(19, '秦二十一', 108, '2022-04-01', 6200.00, '客服专员', 'qin21@example.com', '13001234567'),
(20, '尤二十二', 109, '2021-08-01', 10500.00, '产品经理', 'you22@example.com', '13112345678'),
(21, '许二十三', 101, '2023-05-01', 7800.00, '后端开发', 'xu23@example.com', '13223456789'),
(22, '何二十四', 102, '2022-10-01', 7100.00, '市场分析师', 'he24@example.com', '13334567890'),
(23, '吕二十五', 103, '2021-01-01', 9800.00, '大客户销售', 'lv25@example.com', '13445678901'),
(24, '施二十六', 104, '2023-09-01', 6000.00, '培训专员', 'shi26@example.com', '13556789012'),
(25, '张二十七', 105, '2022-06-01', 9200.00, '高级会计', 'zhang27@example.com', '13667890123'),
(26, '孔二十八', 106, '2021-03-01', 8100.00, '运营经理', 'kong28@example.com', '13778901234'),
(27, '曹二十九', 107, '2023-04-01', 10800.00, '资深法务', 'cao29@example.com', '13889012345'),
(28, '严三十', 109, '2022-11-01', 9900.00, 'UI/UX设计师', 'yan30@example.com', '13990123456'),
(29, '华三十一', 101, '2021-02-01', 9000.00, 'DevOps工程师', 'hua31@example.com', '13011223344'),
(30, '金三十二', 110, '2020-10-01', 6000.00, '行政助理', 'jin32@example.com', '13122334455'),
(31, '陶三十三', 101, '2023-08-01', 7500.00, '初级软件工程师', 'tao33@example.com', '13233445566'),
(32, '姜三十四', 102, '2024-01-01', 6800.00, '市场助理', 'jiang34@example.com', '13344556677'),
(33, '戚三十五', 103, '2022-05-10', 8900.00, '销售主管', 'qi35@example.com', '13455667788'),
(34, '谢三十六', 104, '2021-07-01', 7200.00, '薪酬福利专员', 'xie36@example.com', '13566778899'),
(35, '邹三十七', 105, '2023-03-01', 8500.00, '成本会计', 'zou37@example.com', '13677889900'),
(36, '喻三十八', 106, '2022-09-01', 7900.00, '内容运营', 'yu38@example.com', '13788990011'),
(37, '柏三十九', 108, '2023-11-01', 6100.00, '客服主管', 'bai39@example.com', '13899001122'),
(38, '水四十', 109, '2022-02-01', 11500.00, '高级产品经理', 'shui40@example.com', '13900112233'),
(39, '窦四十一', 111, '2021-05-01', 7000.00, '技术支持工程师', 'dou41@example.com', '13011223344'),
(40, '章四十二', 112, '2023-07-01', 9000.00, '采购经理', 'zhang42@example.com', '13122334455'),
(41, '云四十三', 113, '2022-08-01', 8000.00, '质量工程师', 'yun43@example.com', '13233445566'),
(42, '苏四十四', 114, '2024-02-01', 7500.00, '公关专员', 'su44@example.com', '13344556677'),
(43, '潘四十五', 115, '2023-06-01', 9500.00, '资深数据分析师', 'pan45@example.com', '13455667788'),
(44, '葛四十六', 101, '2022-04-01', 8700.00, '移动开发工程师', 'ge46@example.com', '13566778899'),
(45, '奚四十七', 102, '2023-10-01', 6900.00, '市场助理', 'xi47@example.com', '13677889900'),
(46, '范四十八', 103, '2021-03-01', 9300.00, '区域销售经理', 'fan48@example.com', '13788990011'),
(47, '彭四十九', 104, '2024-03-01', 6300.00, '行政助理', 'peng49@example.com', '13899001122'),
(48, '郎五十', 105, '2022-12-01', 8900.00, '税务专员', 'lang50@example.com', '13900112233');-- 插入项目数据 (projects)
INSERT INTO projects (project_id, project_name, start_date, end_date, budget) VALUES
(1001, '新产品研发', '2023-01-01', '2023-12-31', 500000.00),
(1002, '市场推广活动', '2023-03-15', '2023-09-30', 150000.00),
(1003, '内部管理系统升级', '2023-05-01', '2024-02-28', 300000.00),
(1004, '销售渠道拓展', '2023-07-01', '2024-06-30', 200000.00),
(1005, '大数据平台建设', '2023-09-01', '2024-08-31', 750000.00),
(1006, '年度品牌宣传', '2024-01-01', '2024-06-30', 250000.00),
(1007, '海外市场调研', '2024-03-01', '2024-05-31', 80000.00),
(1008, '客户满意度提升计划', '2024-02-01', '2024-11-30', 120000.00),
(1009, '移动APP开发', '2024-04-01', '2025-03-31', 600000.00),
(1010, '企业文化建设', '2024-06-01', '2024-12-31', 50000.00),
(1011, 'AI智能客服系统', '2024-07-01', '2025-06-30', 800000.00),
(1012, '年度财务审计', '2024-01-01', '2024-03-31', 30000.00),
(1013, '云计算平台迁移', '2024-08-01', '2025-07-31', 900000.00),
(1014, '新员工入职培训系统', '2024-09-01', '2025-01-31', 70000.00),
(1015, '国际市场拓展', '2024-10-01', '2025-09-30', 400000.00),
(1016, '数据安全强化', '2024-11-01', '2025-05-31', 180000.00);-- 插入员工与项目关联数据 (employee_projects)
INSERT INTO employee_projects (employee_id, project_id, role, hours_worked) VALUES
(1, 1001, '后端开发', 160.00),
(2, 1001, '项目负责人', 180.00),
(6, 1001, '测试', 150.00),
(3, 1002, '文案策划', 120.00),
(7, 1002, '活动组织', 140.00),
(1, 1003, '数据库设计', 80.00),
(2, 1003, '系统架构', 70.00),
(4, 1004, '渠道经理', 170.00),
(9, 1001, '前端开发', 140.00),
(13, 1001, '全栈开发', 175.00),
(2, 1005, '项目负责人', 100.00),
(9, 1005, '数据建模', 130.00),
(13, 1005, '数据工程师', 120.00),
(16, 1005, '数据分析', 140.00),
(43, 1005, '资深数据分析', 160.00),
(3, 1006, '营销策略', 90.00),
(7, 1006, '公关协调', 110.00),
(17, 1006, '品牌推广', 100.00),
(42, 1006, '媒体关系', 80.00),
(10, 1004, '销售支持', 160.00),
(18, 1004, '销售代表', 150.00),
(23, 1004, '销售顾问', 150.00),
(11, 1007, '市场分析', 100.00),
(21, 1009, '后端开发', 160.00),
(20, 1009, '产品负责人', 170.00),
(28, 1009, 'UI/UX设计', 150.00),
(44, 1009, '移动开发', 145.00),
(19, 1008, '客服代表', 130.00),
(11, 1008, '运营支持', 80.00),
(37, 1008, '客服主管', 120.00),
(29, 1003, '部署维护', 100.00),
(8, 1012, '审计协调', 60.00),
(15, 1012, '财务分析', 70.00),
(25, 1012, '高级会计', 80.00),
(1, 1011, 'AI算法开发', 100.00),
(21, 1011, '系统集成', 90.00),
(20, 1011, '产品规划', 80.00),
(38, 1011, '高级产品经理', 70.00),
(31, 1013, '后端开发', 150.00),
(29, 1013, '云架构师', 170.00),
(14, 1014, '培训内容开发', 110.00),
(24, 1014, '培训讲师', 90.00),
(4, 1015, '国际销售策略', 160.00),
(23, 1015, '国际客户经理', 150.00),
(16, 1016, '安全审计', 130.00),
(41, 1016, '质量保障', 120.00);-- 插入员工工资历史数据 (salary_history)
INSERT INTO salary_history (history_id, employee_id, salary, start_date, end_date) VALUES
(1, 1, 7500.00, '2020-01-15', '2021-12-31'),
(2, 1, 8000.00, '2022-01-01', NULL),
(3, 2, 9000.00, '2019-03-20', '2020-12-31'),
(4, 2, 9500.00, '2021-01-01', NULL),
(5, 3, 6500.00, '2021-06-01', '2022-12-31'),
(6, 3, 7000.00, '2023-01-01', NULL),
(7, 4, 11000.00, '2018-11-10', '2022-12-31'),
(8, 4, 12000.00, '2023-01-01', NULL),
(9, 5, 6000.00, '2022-02-28', '2023-12-31'),
(10, 5, 6500.00, '2024-01-01', NULL),
(11, 9, 7800.00, '2021-04-01', '2022-12-31'),
(12, 9, 8200.00, '2023-01-01', NULL),
(13, 10, 8500.00, '2022-08-10', '2023-12-31'),
(14, 10, 9000.00, '2024-01-01', NULL),
(15, 11, 7500.00, '2023-03-15', '2024-06-30'),
(16, 11, 7800.00, '2024-07-01', NULL),
(17, 12, 10500.00, '2022-01-20', '2023-12-31'),
(18, 12, 11000.00, '2024-01-01', NULL),
(19, 13, 8800.00, '2021-11-01', '2023-12-31'),
(20, 13, 9200.00, '2024-01-01', NULL),
(21, 14, 6500.00, '2023-06-01', NULL),
(22, 15, 9500.00, '2020-05-01', '2023-12-31'),
(23, 15, 9800.00, '2024-01-01', NULL),
(24, 16, 8000.00, '2022-07-01', '2023-12-31'),
(25, 16, 8500.00, '2024-01-01', NULL),
(26, 17, 7000.00, '2021-09-10', '2023-12-31'),
(27, 17, 7500.00, '2024-01-01', NULL),
(28, 18, 8500.00, '2023-02-01', NULL),
(29, 19, 6000.00, '2022-04-01', '2023-12-31'),
(30, 19, 6200.00, '2024-01-01', NULL),
(31, 20, 10000.00, '2021-08-01', '2023-12-31'),
(32, 20, 10500.00, '2024-01-01', NULL),
(33, 21, 7500.00, '2023-05-01', NULL),
(34, 22, 6800.00, '2022-10-01', '2023-12-31'),
(35, 22, 7100.00, '2024-01-01', NULL),
(36, 23, 9500.00, '2021-01-01', '2023-12-31'),
(37, 23, 9800.00, '2024-01-01', NULL),
(38, 24, 5800.00, '2023-09-01', NULL),
(39, 25, 8800.00, '2022-06-01', '2023-12-31'),
(40, 25, 9200.00, '2024-01-01', NULL),
(41, 26, 7800.00, '2021-03-01', '2023-12-31'),
(42, 26, 8100.00, '2024-01-01', NULL),
(43, 27, 10500.00, '2023-04-01', NULL),
(44, 28, 9500.00, '2022-11-01', '2023-12-31'),
(45, 28, 9900.00, '2024-01-01', NULL),
(46, 29, 8500.00, '2021-02-01', '2023-12-31'),
(47, 29, 9000.00, '2024-01-01', NULL),
(48, 30, 5800.00, '2020-10-01', '2023-12-31'),
(49, 30, 6000.00, '2024-01-01', NULL),
(50, 31, 7000.00, '2023-08-01', NULL),
(51, 32, 6500.00, '2024-01-01', NULL),
(52, 33, 8500.00, '2022-05-10', '2023-12-31'),
(53, 33, 8900.00, '2024-01-01', NULL),
(54, 34, 7000.00, '2021-07-01', '2023-12-31'),
(55, 34, 7200.00, '2024-01-01', NULL),
(56, 35, 8000.00, '2023-03-01', NULL),
(57, 36, 7500.00, '2022-09-01', '2023-12-31'),
(58, 36, 7900.00, '2024-01-01', NULL),
(59, 37, 6000.00, '2023-11-01', NULL),
(60, 38, 11000.00, '2022-02-01', '2023-12-31'),
(61, 38, 11500.00, '2024-01-01', NULL),
(62, 39, 6800.00, '2021-05-01', '2023-12-31'),
(63, 39, 7000.00, '2024-01-01', NULL),
(64, 40, 8500.00, '2023-07-01', NULL),
(65, 41, 7800.00, '2022-08-01', '2023-12-31'),
(66, 41, 8000.00, '2024-01-01', NULL),
(67, 42, 7200.00, '2024-02-01', NULL),
(68, 43, 9000.00, '2023-06-01', '2023-12-31'),
(69, 43, 9500.00, '2024-01-01', NULL),
(70, 44, 8200.00, '2022-04-01', '2023-12-31'),
(71, 44, 8700.00, '2024-01-01', NULL),
(72, 45, 6600.00, '2023-10-01', NULL),
(73, 46, 9000.00, '2021-03-01', '2023-12-31'),
(74, 46, 9300.00, '2024-01-01', NULL),
(75, 47, 6000.00, '2024-03-01', NULL),
(76, 48, 8500.00, '2022-12-01', '2023-12-31'),
(77, 48, 8900.00, '2024-01-01', NULL);