从 MySQL 迁移到 ProtonBase 指南

-- 使用具有足够权限的账户登录 MySQL
mysql -h ${mysql_host} -u root -p
 
-- 创建同步专用用户
CREATE USER 'teleport_user'@'%' IDENTIFIED BY 'secure_password';
 
-- 授予必要的权限
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'teleport_user'@'%';
 
-- 刷新权限
FLUSH PRIVILEGES;

-- 在 ProtonBase 中创建用户
CREATE USER "migration_user" WITH PASSWORD 'secure_password';
 
-- 授予数据库权限
GRANT ALL PRIVILEGES ON DATABASE ecommerce TO "migration_user";

-- MySQL 中可以执行
SELECT a, b, sum(c) FROM t GROUP BY a;
 
-- ProtonBase 中需要修改为
SELECT a, b, sum(c) FROM t GROUP BY a, b;
-- 或者使用聚合函数
SELECT a, any(b), sum(c) FROM t GROUP BY a;

-- MySQL 中可以执行
CREATE TABLE t1(a int);
CREATE TABLE t2(b text);
SELECT a FROM t1 UNION ALL SELECT b FROM t2;
 
-- ProtonBase 中需要显式转换类型
CREATE TABLE t1(a int);
CREATE TABLE t2(b text);
SELECT a::text FROM t1 UNION ALL SELECT b FROM t2;

-- 创建扩展
CREATE EXTENSION mysql;
CREATE EXTENSION
 
-- 验证扩展是否生效（注：部分功能需要重连才会生效）
SELECT ifnull(null, 2);
 ifnull
--------
      2
 
-- 删除扩展
DROP EXTENSION mysql;

test=> CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
CREATE FUNCTION
test=> SELECT add(1,1);
 add
-----
   2
(1 row)

CREATE CAST (smallint AS boolean) WITH INOUT AS IMPLICIT;

-- 启用扩展后可执行
SELECT department, name, AVG(salary)
FROM employees
GROUP BY department;  -- name 未在 GROUP BY 中

SELECT 10 / 0;  -- 返回 NULL

-- 检查MySQL数据库大小
SELECT 
    table_schema AS "Database",
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables
GROUP BY table_schema;
 
-- 检查表结构
SHOW CREATE TABLE users;
SHOW CREATE TABLE orders;

-- 在ProtonBase中创建数据库
CREATE DATABASE ecommerce;
 
-- 创建用户和权限
CREATE USER "migration_user" WITH PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE ecommerce TO "migration_user";

-- MySQL中的表结构
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- 对应的ProtonBase表结构
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- MySQL中的ENUM类型
CREATE TABLE order_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'processing', 'shipped', 'delivered') NOT NULL
);
 
-- ProtonBase中的等效实现
CREATE TABLE order_status (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'processing', 'shipped', 'delivered'))
);

-- 检查表行数
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;
 
-- 检查数据完整性
SELECT * FROM users LIMIT 10;
SELECT * FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

# 原MySQL配置
# jdbc:mysql://mysql-host:3306/ecommerce
 
# 新ProtonBase配置
jdbc:postgresql://protonbase-host:5432/ecommerce

// 原MySQL分页查询
String mysqlQuery = "SELECT * FROM users LIMIT 20, 10";
 
// ProtonBase分页查询
String protonbaseQuery = "SELECT * FROM users OFFSET 20 LIMIT 10";

-- 测试基本查询
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.username;
 
-- 测试聚合查询
SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as user_count
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- 测试查询性能
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE user_id = 12345 
AND created_at >= '2024-01-01';

-- 在应用层处理数据范围验证
-- 或者在ProtonBase中添加CHECK约束
ALTER TABLE my_table 
ADD CONSTRAINT valid_tinyint 
CHECK (my_column >= -128 AND my_column <= 127);

-- 使用ORM框架自动处理语法差异
-- 或者在应用中适配SQL语法
-- MySQL: LIMIT 10, 20
-- ProtonBase: OFFSET 10 LIMIT 20

-- 在ProtonBase中明确指定字符集和排序规则
CREATE TABLE my_table (
    name VARCHAR(100) COLLATE "en_US.utf8"
);