作者：Digital Observer（施嘉偉）
Oracle ACE Pro: Database
PostgreSQL ACE Partner
11年數據庫行業經驗，現主要從事數據庫服務工作
擁有Oracle OCM、KCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、PGCM、OCI、PolarDB技術專家、達夢師資認證、數據安全咨詢高級等認證
ITPUB認證專家、PolarDB開源社區技術顧問、HaloDB技術顧問、TiDB社區技術布道師、青學會MOP技術社區專家顧問、國內某高校企業實踐指導教師
公眾號：Digital Observer；CSDN：施嘉偉；ITPUB：sjw1933；墨天輪：Digital Observer；PGFans：施嘉偉。

為了驗證金倉數據庫（MySQL兼容版）和 MySQL 數據庫在以下?DML 操作?和?數據查詢?語句的兼容性，我通過以下測試來確保兩者的兼容性：

• DML 操作：包括?INSERT IGNORE、LIMIT?子句、INSERT ON DUPLICATE KEY、REPLACE INTO?和?LOAD DATA INFILE。
• 數據查詢語句：包括?GROUP BY WITH ROLLUP?來生成多級匯總行。

1. 測試思路

1. 執行相同的 DML 操作：
   • 確保在 MySQL 和金倉數據庫中執行相同的 DML 操作（例如?INSERT IGNORE、LIMIT、ON DUPLICATE KEY、REPLACE INTO?等），檢查結果是否一致。
2. 驗證?LOAD DATA INFILE：
   • 檢查兩個數據庫是否能夠正確加載文件，并且數據在表中的插入順序和內容一致。
3. 執行數據查詢：
   • 執行?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.047s

mysql執行結果：

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 /backup

chown -R kingbase:kingbase /KingbaseES/V9
chown -R kingbase:kingbase /data
chown -R kingbase:kingbase /backup
chown -R kingbase:kingbase /install

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

![hhh6.jpg](https://oss-emcsprod-public.modb.pro/image/editor/20250424-1915246539937689600_441010.jpg)