异构数据库兼容力测评：KingbaseES 与 MySQL 的语法・功能・性能全场景验证解析
一、安装kingbase(mysql兼容版)
下载地址：https://www.kingbase.com.cn/download.html
下载安装包和授权文件
检查系统基本信息
[root@localhost ~]# cat /etc/os-release
[root@localhost ~]# free -m
[root@localhost ~]# df -hl
配置内核参数
[root@localhost ~]# vi /etc/sysctl.conf
添加以下内容
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
fs.file-max = 6815744
[root@localhost ~]#sysctl -p
设置系统资源限制
[root@localhost ~]# vi /etc/security/limits.conf
添加以下内容
* soft nofile 65536
* hard nofile 65535
* soft nproc 65536
* hard nproc 65535
* soft core unlimited
* hard core unlimited
创建数据库专用安装用户
[root@localhost ~]# useradd -m kingbase
[root@localhost ~]# passwd kingbase #这里密码要输入强密码
创建安装目录
[root@localhost ~]# sudo mkdir -p /opt/Kingbase/ES/V9
[root@localhost ~]# sudo chown -R kingbase:kingbase /opt/Kingbase/ES/V9
[root@localhost ~]# sudo chmod -R 755 /opt/Kingbase/ES/V9
[root@localhost ~]# mkdir -p /opt/Kingbase/ES/V9/data
[root@localhost ~]# sudo chown -R kingbase:kingbase /opt/Kingbase/ES/V9/data
[root@localhost ~]# sudo chmod -R 755 /opt/Kingbase/ES/V9/data
创建数据目录
[root@localhost ~]# sudo mkdir -p /data/kingbase
[root@localhost ~]# sudo chown -R kingbase:kingbase /data/kingbase
[root@localhost ~]# sudo chmod -R 750 /data/kingbase
挂载安装包
[root@localhost ~]# mkdir -p /mnt/kingbase
[root@localhost ~]# mount KingbaseES_V009R003C011B0003_Lin64_install.iso /mnt/kingbase/
命令行安装步骤
[root@localhost ~]# su - kingbase
[kingbase@localhost ~]$ cd /mnt/kingbase
[kingbase@localhost kingbase]$ sh setup.sh
这里授权文件要给kingbase的权限，要不然会报错
[root@localhost V9]# /opt/Kingbase/ES/V9/install/script/root.sh
到这里就完成安装了
查看服务状态
[kingbase@localhost kingbase]$ ps -ef | grep kingbase
[kingbase@localhost kingbase]$ /opt/Kingbase/ES/V9/Server/bin/sys_ctl -w start -D /data/kingbase/ #启动数据库
[kingbase@localhost kingbase]$ /opt/Kingbase/ES/V9/Server/bin/sys_ctl stop -m fast -w -D /data/kingbase/#停止数据库
登录数据库
[kingbase@localhost kingbase]$ /opt/Kingbase/ES/V9/Server/bin/ksql -p 54321 -U system -d kingbase
二、测试兼容性
创建测试数据
-- 1. 创建customers表
CREATE TABLE customers (id INT PRIMARY KEY,name VARCHAR(50),credit DECIMAL(10,2)
);-- 2. 创建orders表
CREATE TABLE orders (id INT PRIMARY KEY,cust_id INT REFERENCES customers(id),status VARCHAR(20),amount DECIMAL(10,2)
);-- 3. 插入测试数据
INSERT INTO customers (id, name, credit) VALUES
(1001, 'Alice', 5000.00),
(1002, 'Bob', 3000.00);INSERT INTO orders (id, cust_id, status, amount) VALUES
(2001, 1001, 'pending', 100.00),
(2002, 1001, 'pending', 200.00),
(2003, 1002, 'shipped', 150.00);
（1）金仓数据库：
kingbase=# show tables;
（2）MySQL数据库
一、验证多表更新兼容性
-- 验证多表更新兼容性
UPDATE orders o, customers c
SET o.status = 'shipped', c.credit = c.credit - o.amount
WHERE o.cust_id = c.id AND o.id = 2001;-- 检查更新结果
SELECT * FROM orders WHERE id = 2001;
SELECT * FROM customers WHERE id = 1001;
（1）金仓数据库
（2）MySQL数据库
二、INSERT IGNORE语句
金仓和MySQL数据库上创建测试数据
CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(50) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (id, name) VALUES (1, 'Alice');
执行INSERT IGNORE语句
INSERT IGNORE INTO users (id, name) VALUES (1, 'Alice'), (1, 'Bob') ;
（1）金仓数据库
（2）MySQL数据库
三、LIMIT语句
UPDATE customers
SET credit = 5000.00
WHERE id = 1001
LIMIT 2;
（1）金仓数据库
（2）MySQL数据库
四、INSERT ON DUPLICATE KEY UPDATE语句
-- 测试UPSERT功能（冲突时更新）
INSERT INTO users (id, name) VALUES (1, 'Bob')
ON DUPLICATE KEY UPDATE name = 'Updated';-- 验证更新结果
SELECT * FROM users WHERE id=1;
（1）金仓数据库
（2）MySQL数据库
五、REPLACE INTO语句
-- 测试替换插入
REPLACE INTO users (id, name) VALUES (2, 'Charlie'), (1, 'Replaced');-- 验证结果
SELECT * FROM users ORDER BY id;
（1）金仓数据库
（2）MySQL数据库
六、LOAD DATA INFILE 语句
LOAD DATA INFILE '/data/kingbase/t.txt' INTO TABLE t;
（1）金仓数据库
（2）MySQL数据库
七、GROUP BY WITH ROLLUP语句
CREATE TABLE sales (region VARCHAR(20),product VARCHAR(20),year INT,month INT,amount DECIMAL(10,2)
);INSERT INTO sales (region, product, year, month, amount) VALUES
('North', 'Phone', 2023, 1, 1000.00),
('North', 'Phone', 2023, 2, 1500.00),
('North', 'Laptop', 2023, 1, 2000.00),
('North', 'Laptop', 2023, 2, 2500.00),
('South', 'Phone', 2023, 1, 800.00),
('South', 'Phone', 2023, 2, 1200.00),
('South', 'Tablet', 2023, 1, 1500.00),
('South', 'Tablet', 2023, 2, 1800.00),
('East', 'Phone', 2023, 1, 900.00);
测试语句
SELECT year, month, product,SUM(amount) AS total_sales,COUNT(*) AS transactions
FROM sales
GROUP BY year, month, product WITH ROLLUP;
（1）金仓数据库
（2）MySQL数据库
