Java將MySQL建表語句轉換為SQLite的建表語句

源代碼：

package com.fxsen.platform.core.util;

import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * mysql轉SQLite
 *@Author: <a href="mailto:[email protected]">Fxsen</a>
 *@CreateTime: 2023年08月22日  15:46
 */
public class MysqlToSqLiteUtil {

    public static final Map<String, String> FIELD_TYPE_MAP = new HashMap<>();

    static {
        FIELD_TYPE_MAP.put("int", "INTEGER");
        FIELD_TYPE_MAP.put("bigint", "INTEGER");
        FIELD_TYPE_MAP.put("tinyint", "INTEGER");
        FIELD_TYPE_MAP.put("smallint", "INTEGER");
        FIELD_TYPE_MAP.put("mediumint", "INTEGER");
        FIELD_TYPE_MAP.put("float", "REAL");
        FIELD_TYPE_MAP.put("double", "REAL");
        FIELD_TYPE_MAP.put("decimal", "NUMERIC");
        FIELD_TYPE_MAP.put("varchar", "TEXT");
        FIELD_TYPE_MAP.put("char", "TEXT");
        FIELD_TYPE_MAP.put("text", "TEXT");
        FIELD_TYPE_MAP.put("enum", "TEXT");
        FIELD_TYPE_MAP.put("datetime", "TEXT");
        FIELD_TYPE_MAP.put("timestamp", "TEXT");
        FIELD_TYPE_MAP.put("date", "TEXT");
        FIELD_TYPE_MAP.put("time", "TEXT");
        FIELD_TYPE_MAP.put("blob", "BLOB");
        FIELD_TYPE_MAP.put("bit", "TEXT");
        FIELD_TYPE_MAP.put("boolean", "INTEGER");
        FIELD_TYPE_MAP.put("set", "TEXT");
        FIELD_TYPE_MAP.put("json", "TEXT");
        FIELD_TYPE_MAP.put("geometry", "BLOB");
        FIELD_TYPE_MAP.put("point", "BLOB");
        FIELD_TYPE_MAP.put("linestring", "BLOB");
        FIELD_TYPE_MAP.put("polygon", "BLOB");
        FIELD_TYPE_MAP.put("multipoint", "BLOB");
        FIELD_TYPE_MAP.put("multilinestring", "BLOB");
        FIELD_TYPE_MAP.put("multipolygon", "BLOB");
        FIELD_TYPE_MAP.put("geometrycollection", "BLOB");
    }

    public static void main(String[] args) {
        String createTableStatement = "CREATE TABLE `t_enterprise_info` (\n" +
                "  `id` varchar(32) NOT NULL COMMENT '主鍵ID',\n" +
                "  `category_id` text COMMENT '企業類別ID',\n" +
                "  `name` varchar(255) DEFAULT NULL COMMENT '企業名稱',\n" +
                "  `social_credit_code` varchar(255) DEFAULT NULL COMMENT '社會信用代碼',\n" +
                "  `org_code` varchar(255) DEFAULT NULL COMMENT '組織機構代碼',\n" +
                "  `address` varchar(255) DEFAULT NULL COMMENT '街(村)、門牌號（實際經營地）',\n" +
                "  `register_address` varchar(255) DEFAULT NULL COMMENT '單位註冊地',\n" +
                "  `legal_person` varchar(255) DEFAULT NULL COMMENT '單位法人',\n" +
                "  `open_date` varchar(50) DEFAULT NULL COMMENT '開業時間',\n" +
                "  PRIMARY KEY (`id`) USING BTREE\n" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='企業基本信息表';";
        System.out.println(convertMysqlToSQLite(createTableStatement));
    }

    public static String convertMysqlToSQLite(String mysqlStatement) {

        // 刪除COMMENT
        String reg = "COMMENT\\s*'.*?'";
        Pattern pattern = Pattern.compile(reg);
        Matcher matcher = pattern.matcher(mysqlStatement);
        String result = matcher.replaceAll("")
                .replaceAll("\\).*?;", ");")
                .replaceAll("USING BTREE","")
                .replaceAll("`","\"");
        // 替換 MySQL 建表語句中的數據類型和關鍵字為 SQLite 的等價語句
        for (String key : FIELD_TYPE_MAP.keySet()) {
            // 生成正則表達式，匹配欄位類型
            String regex = "\\b" + key + "\\b";
            Pattern pattern2 = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
            Matcher matcher2 = pattern2.matcher(result);

            // 執行替換
            result = matcher2.replaceAll(FIELD_TYPE_MAP.get(key));
        }

        // 返回替換後的 SQLite 建表語句
        return result;
    }

轉換前：

CREATE TABLE `t_enterprise_info` (
  `id` varchar(32) NOT NULL COMMENT '主鍵ID',
  `category_id` text COMMENT '企業類別ID',
  `name` varchar(255) DEFAULT NULL COMMENT '企業名稱',
  `social_credit_code` varchar(255) DEFAULT NULL COMMENT '社會信用代碼',
  `org_code` varchar(255) DEFAULT NULL COMMENT '組織機構代碼',
  `address` varchar(255) DEFAULT NULL COMMENT '街(村)、門牌號（實際經營地）',
  `register_address` varchar(255) DEFAULT NULL COMMENT '單位註冊地',
  `legal_person` varchar(255) DEFAULT NULL COMMENT '單位法人',
  `open_date` varchar(50) DEFAULT NULL COMMENT '開業時間',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='企業基本信息表';

轉換後：

CREATE TABLE "t_enterprise_info" (
  "id" TEXT(32) NOT NULL ,
  "category_id" TEXT ,
  "name" TEXT(255) DEFAULT NULL ,
  "social_credit_code" TEXT(255) DEFAULT NULL ,
  "org_code" TEXT(255) DEFAULT NULL ,
  "address" TEXT(255) DEFAULT NULL ,
  "register_address" TEXT(255) DEFAULT NULL ,
  "legal_person" TEXT(255) DEFAULT NULL ,
  "open_date" TEXT(50) DEFAULT NULL ,
  PRIMARY KEY ("id") 
);