github.com/team-ide/go-dialect@v1.9.20/config/dialect.mysql.mapping.sql (about) 1 -- 数据库方言SQL -- 2 3 -- 库、表所属者相关SQL -- 4 5 -- owner create sql start -- 6 CREATE DATABASE [IF NOT EXISTS] {ownerName} 7 [[DEFAULT] CHARACTER SET {characterSetName}] 8 [[DEFAULT] COLLATE {collationName}] 9 -- owner create sql end -- 10 11 -- owners select sql start -- 12 SELECT 13 SCHEMA_NAME name, 14 DEFAULT_CHARACTER_SET_NAME characterSetName, 15 DEFAULT_COLLATION_NAME collationName 16 FROM information_schema.schemata 17 ORDER BY SCHEMA_NAME 18 -- owners select sql end -- 19 20 -- owner select sql start -- 21 SELECT 22 SCHEMA_NAME name, 23 DEFAULT_CHARACTER_SET_NAME characterSetName, 24 DEFAULT_COLLATION_NAME collationName 25 FROM information_schema.schemata 26 WHERE SCHEMA_NAME='{ownerName}' 27 -- owner select sql end -- 28 29 -- owner delete sql start -- 30 DROP DATABASE IF EXISTS {ownerName} 31 -- owner delete sql end -- 32 33 -- owner delete sql start -- 34 DROP DATABASE IF EXISTS {ownerName} 35 -- owner delete sql end -- 36 37 -- 表相关SQL -- 38 39 -- tables select sql start -- 40 SELECT 41 TABLE_NAME name, 42 TABLE_COMMENT comment, 43 TABLE_SCHEMA ownerName 44 FROM information_schema.tables 45 WHERE TABLE_SCHEMA='{ownerName}' 46 ORDER BY TABLE_NAME 47 -- tables select sql end -- 48 49 -- table select sql start -- 50 SELECT 51 TABLE_NAME name, 52 TABLE_COMMENT comment, 53 TABLE_SCHEMA ownerName 54 FROM information_schema.tables 55 WHERE TABLE_SCHEMA='{ownerName}' 56 AND TABLE_NAME='{tableName}' 57 -- table select sql end -- 58 59 -- table create sql start -- 60 CREATE TABLE [{ownerName}.]{tableName}( 61 {tableCreateColumns} 62 ) 63 -- table create sql end -- 64 65 -- table create column sql start -- 66 {columnName} {columnType} [CHARACTER SET {characterSetName}] [DEFAULT {default}] [NOT NULL] 67 -- table create column sql end -- 68 69 -- table comment sql start -- 70 ALTER TABLE [{ownerName}.]{tableName} COMMENT '{tableComment}' 71 -- table comment sql end -- 72 73 -- table rename sql start -- 74 ALTER TABLE [{ownerName}.]{oldTableName} RENAME AS {newTableName} 75 -- table rename sql end -- 76 77 -- table delete sql start -- 78 DROP TABLE IF EXISTS [{ownerName}.]{tableName} 79 -- table delete sql end -- 80 81 -- 字段相关SQL -- 82 83 -- columns select sql start -- 84 SELECT 85 COLUMN_NAME columnName, 86 COLUMN_COMMENT columnComment, 87 COLUMN_DEFAULT columnDefault, 88 EXTRA columnExtra, 89 TABLE_NAME tableName, 90 TABLE_SCHEMA ownerName, 91 CHARACTER_SET_NAME characterSetName, 92 IS_NULLABLE isNullable, 93 COLUMN_TYPE columnType, 94 DATA_TYPE dataType 95 FROM information_schema.columns 96 WHERE TABLE_SCHEMA='{ownerName}' 97 AND TABLE_NAME='{tableName}' 98 -- columns select sql end -- 99 100 -- column select sql start -- 101 SELECT 102 COLUMN_NAME columnName, 103 COLUMN_COMMENT columnComment, 104 COLUMN_DEFAULT columnDefault, 105 EXTRA columnExtra, 106 TABLE_NAME tableName, 107 TABLE_SCHEMA ownerName, 108 CHARACTER_SET_NAME characterSetName, 109 IS_NULLABLE isNullable, 110 COLUMN_TYPE columnType, 111 DATA_TYPE dataType 112 FROM information_schema.columns 113 WHERE TABLE_SCHEMA='{ownerName}' 114 AND TABLE_NAME='{tableName}' 115 AND TABLE_NAME='{tableName}' 116 AND COLUMN_NAME='{columnName}' 117 -- column select sql end -- 118 119 -- column add sql start -- 120 ALTER TABLE [{ownerName}.]{tableName} ADD COLUMN {columnName} {columnType} [CHARACTER SET {characterSetName}] [DEFAULT {columnDefault}] [NOT NULL] [COMMENT {columnComment}] 121 -- column add sql end -- 122 123 -- column comment sql start -- 124 ALTER TABLE [{ownerName}.]{tableName} CHANGE COLUMN {columnName} {columnName} {columnType} [CHARACTER SET {characterSetName}] [DEFAULT {columnDefault}] [NOT NULL] [COMMENT {columnComment}] 125 -- column comment sql end -- 126 127 -- column rename sql start -- 128 ALTER TABLE [{ownerName}.]{tableName} CHANGE COLUMN {oldColumnName} {newColumnName} {columnType} [CHARACTER SET {characterSetName}] [DEFAULT {columnDefault}] [NOT NULL] [COMMENT {columnComment}] 129 -- column rename sql end -- 130 131 -- column update sql start -- 132 ALTER TABLE [{ownerName}.]{tableName} CHANGE COLUMN {columnName} {columnName} {columnType} [CHARACTER SET {characterSetName}] [DEFAULT {columnDefault}] [NOT NULL] [COMMENT {columnComment}] [AFTER {columnAfter}] 133 -- column update sql end -- 134 135 -- column delete sql start -- 136 ALTER TABLE [{ownerName}.]{tableName} DROP COLUMN {columnName} 137 -- column delete sql end -- 138 139 -- 主键相关SQL -- 140 141 -- primary keys select sql start -- 142 SELECT 143 key_column_usage.COLUMN_NAME columnName, 144 table_constraints.TABLE_NAME tableName, 145 table_constraints.TABLE_SCHEMA ownerName 146 FROM information_schema.table_constraints 147 JOIN information_schema.key_column_usage USING (CONSTRAINT_NAME,TABLE_SCHEMA,TABLE_NAME) 148 WHERE table_constraints.TABLE_SCHEMA='{ownerName}' 149 AND table_constraints.TABLE_NAME='{tableName}' 150 AND table_constraints.CONSTRAINT_TYPE='PRIMARY KEY' 151 -- primary keys select sql end -- 152 153 -- primary key add sql start -- 154 ALTER TABLE [{ownerName}.]{tableName} ADD PRIMARY KEY ({columnNames}) 155 -- primary key add sql end -- 156 157 -- primary key delete sql start -- 158 ALTER TABLE [{ownerName}.]{tableName} DROP PRIMARY KEY 159 -- primary key delete sql end -- 160 161 -- 索引相关SQL -- 162 163 -- indexes select sql start -- 164 SELECT 165 INDEX_NAME indexName, 166 COLUMN_NAME columnName, 167 INDEX_COMMENT indexComment, 168 NON_UNIQUE nonUnique, 169 TABLE_NAME tableName, 170 TABLE_SCHEMA ownerName 171 FROM information_schema.statistics 172 WHERE TABLE_SCHEMA='{ownerName}' 173 AND TABLE_NAME='{tableName}' 174 AND TABLE_NAME='{tableName}' 175 AND INDEX_NAME NOT IN( 176 SELECT table_constraints.CONSTRAINT_NAME 177 FROM information_schema.table_constraints 178 JOIN information_schema.key_column_usage USING (CONSTRAINT_NAME,TABLE_SCHEMA,TABLE_NAME) 179 WHERE table_constraints.TABLE_SCHEMA='{ownerName}' 180 AND table_constraints.TABLE_NAME='{tableName}' 181 AND table_constraints.CONSTRAINT_TYPE='PRIMARY KEY' 182 ) 183 184 -- indexes select sql end -- 185 186 -- index add sql start -- 187 ALTER TABLE [{ownerName}.]{tableName} ADD [PRIMARY KEY | UNIQUE | FULLTEXT | INDEX] {indexName} ({columnNames}) [COMMENT {columnComment}] 188 -- index add sql end -- 189 190 -- index delete sql start -- 191 ALTER TABLE [{ownerName}.]{tableName} DROP INDEX 192 -- index delete sql end --