达梦数据库 DM8 与 MySQL 差异全解析：迁移避坑指南

-- MySQL
CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

-- DM8
CREATE TABLE users (
  id BIGINT IDENTITY(1,1) PRIMARY KEY,
  name VARCHAR2(100)
);

-- MySQL
SELECT * FROM users LIMIT 10 OFFSET 20;

-- DM8（兼容 LIMIT 或使用 ROWNUM）
SELECT * FROM users LIMIT 10 OFFSET 20;

-- DM8 传统写法
SELECT * FROM (
  SELECT t.*, ROWNUM rn FROM (
    SELECT * FROM users ORDER BY id
  ) t WHERE ROWNUM <= 30
) WHERE rn > 20;

-- MySQL
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');

-- DM8
INSERT ALL
  INTO users (name) VALUES ('Alice')
  INTO users (name) VALUES ('Bob')
  INTO users (name) VALUES ('Charlie')
SELECT 1 FROM dual;

-- MySQL
UPDATE t1, t2 SET t1.col = t2.col WHERE t1.id = t2.id;

-- DM8（使用 MERGE INTO）
MERGE INTO t1 USING t2 ON (t1.id = t2.id)
WHEN MATCHED THEN UPDATE SET t1.col = t2.col;

-- MySQL
DELETE t1 FROM t1, t2 WHERE t1.id = t2.id AND t2.status = 0;

-- DM8
DELETE FROM t1 WHERE id IN (SELECT id FROM t2 WHERE status = 0);

-- MySQL
ALTER TABLE users CHANGE name full_name VARCHAR(100);

-- DM8
ALTER TABLE users RENAME COLUMN name TO full_name;

-- MySQL
ALTER TABLE users DROP COLUMN age;

-- DM8
ALTER TABLE users DROP age;

-- MySQL 可以执行（但结果不确定）
SELECT id, name, COUNT(*) FROM users GROUP BY department;

-- DM8 必须这样写
SELECT department, COUNT(*) FROM users GROUP BY department;

-- MySQL：HAVING 可以引用 SELECT 别名
SELECT name AS alias_name, COUNT(*) AS cnt
FROM users GROUP BY alias_name HAVING cnt > 5;

-- DM8：HAVING 不能引用别名，必须用原始表达式
SELECT name AS alias_name, COUNT(*) AS cnt
FROM users GROUP BY name HAVING COUNT(*) > 5;

-- MySQL：单引号
SELECT name AS '姓名' FROM users;

-- DM8：双引号
SELECT name AS "姓名" FROM users;

-- MySQL 可以执行（隐式转换）
SELECT * FROM users WHERE phone = 13800138000;

-- DM8 必须类型一致
SELECT * FROM users WHERE phone = '13800138000';

# MySQL
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

# DM8
spring.datasource.driver-class-name=dm.jdbc.driver.DmDriver
spring.datasource.url=jdbc:dm://localhost:5236
spring.jpa.database-platform=dm.hibernate.dialect.DmDialect

jdbc:dm://localhost:5236?schema=MY_SCHEMA

# 初始化实例时指定字符集（CHARSET：0=GB18030，1=UTF-8，2=EUC-KR）
dminit PATH=/dm/data DB_NAME=mydb INSTANCE_NAME=mydb CHARSET=1 PAGE_SIZE=16