Created
November 28, 2025 21:03
-
-
Save qichunren/27e0850dbefab0a1cada83e49f701549 to your computer and use it in GitHub Desktop.
将mysql中的表构建生成MySQL engine的clickhouse表结构
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT CONCAT( | |
| 'CREATE TABLE recharges_remote (\n', | |
| GROUP_CONCAT( | |
| CONCAT(' `', COLUMN_NAME, '` ', | |
| CASE | |
| WHEN DATA_TYPE IN ('tinyint', 'smallint', 'mediumint', 'int') AND COLUMN_TYPE LIKE '%unsigned%' THEN | |
| CASE | |
| WHEN DATA_TYPE = 'tinyint' THEN 'UInt8' | |
| WHEN DATA_TYPE = 'smallint' THEN 'UInt16' | |
| WHEN DATA_TYPE = 'mediumint' THEN 'UInt32' | |
| ELSE 'UInt32' | |
| END | |
| WHEN DATA_TYPE IN ('tinyint', 'smallint', 'mediumint', 'int') THEN | |
| CASE | |
| WHEN DATA_TYPE = 'tinyint' THEN 'Int8' | |
| WHEN DATA_TYPE = 'smallint' THEN 'Int16' | |
| WHEN DATA_TYPE = 'mediumint' THEN 'Int32' | |
| ELSE 'Int32' | |
| END | |
| WHEN DATA_TYPE = 'bigint' AND COLUMN_TYPE LIKE '%unsigned%' THEN 'UInt64' | |
| WHEN DATA_TYPE = 'bigint' THEN 'Int64' | |
| WHEN DATA_TYPE = 'decimal' THEN REPLACE(COLUMN_TYPE, 'decimal', 'Decimal') | |
| WHEN DATA_TYPE = 'datetime' THEN 'DateTime' | |
| WHEN DATA_TYPE = 'date' THEN 'Date' | |
| WHEN DATA_TYPE IN ('varchar', 'text', 'char', 'json', 'enum', 'set') THEN 'String' | |
| WHEN DATA_TYPE IN ('float', 'double') THEN | |
| CASE DATA_TYPE | |
| WHEN 'float' THEN 'Float32' | |
| ELSE 'Float64' | |
| END | |
| ELSE 'String' -- 默认兜底，复杂类型用 String | |
| END, | |
| IF(IS_NULLABLE = 'YES', ' Nullable', '') | |
| ) | |
| ORDER BY ORDINAL_POSITION | |
| SEPARATOR ',\n' | |
| ), | |
| '\n) ENGINE = MySQL(\'mysql-host:3306\', \'catalyst_admin_backend_development\', \'recharges\', \'root\', \'123456\');' | |
| ) AS ch_ddl | |
| FROM INFORMATION_SCHEMA.COLUMNS | |
| WHERE TABLE_SCHEMA = 'catalyst_admin_backend_development' -- 替换成你的数据库名 | |
| AND TABLE_NAME = 'recharges'; -- 替换成你的表名 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment