github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/mysql_ddl_2.result (about) 1 create database if not exists mysql_ddl_test_db; 2 use mysql_ddl_test_db; 3 create table if not exists mmysql_ddl_test_t21(id int, name varchar(255)) engine = 'InnoDB'; 4 show create table mmysql_ddl_test_t21; 5 Table Create Table 6 mmysql_ddl_test_t21 CREATE TABLE `mmysql_ddl_test_t21` (\n`id` INT DEFAULT NULL,\n`name` VARCHAR(255) DEFAULT NULL\n) 7 create table if not exists mmysql_ddl_test_t22(id int, name varchar(255)) DEFAULT CHARSET=utf8 COLLATE = utf8mb4_general_ci ; 8 show create table mmysql_ddl_test_t22; 9 Table Create Table 10 mmysql_ddl_test_t22 CREATE TABLE `mmysql_ddl_test_t22` (\n`id` INT DEFAULT NULL,\n`name` VARCHAR(255) DEFAULT NULL\n) 11 create table if not exists mmysql_ddl_test_t23(id int, name varchar(255)) DEFAULT CHARSET = utf8; 12 show create table mmysql_ddl_test_t23; 13 Table Create Table 14 mmysql_ddl_test_t23 CREATE TABLE `mmysql_ddl_test_t23` (\n`id` INT DEFAULT NULL,\n`name` VARCHAR(255) DEFAULT NULL\n) 15 create table if not exists mmysql_ddl_test_t24(id int, name varchar(255)) DEFAULT CHARSET= utf8; 16 show create table mmysql_ddl_test_t24; 17 Table Create Table 18 mmysql_ddl_test_t24 CREATE TABLE `mmysql_ddl_test_t24` (\n`id` INT DEFAULT NULL,\n`name` VARCHAR(255) DEFAULT NULL\n) 19 create table if not exists mmysql_ddl_test_t25(id int, name varchar(255)) DEFAULT CHARSET =utf8; 20 show create table mmysql_ddl_test_t25; 21 Table Create Table 22 mmysql_ddl_test_t25 CREATE TABLE `mmysql_ddl_test_t25` (\n`id` INT DEFAULT NULL,\n`name` VARCHAR(255) DEFAULT NULL\n) 23 create table if not exists mmysql_ddl_test_t26(id int, name varchar(255)) DEFAULT CHARSET = utf8 COLLATE=utf8mb4_general_ci ; 24 show create table mmysql_ddl_test_t26; 25 Table Create Table 26 mmysql_ddl_test_t26 CREATE TABLE `mmysql_ddl_test_t26` (\n`id` INT DEFAULT NULL,\n`name` VARCHAR(255) DEFAULT NULL\n) 27 create table if not exists mmysql_ddl_test_t27(id int, name varchar(255) CHARACTER SET utf8 COLLATE utf8mb4_general_ci); 28 show create table mmysql_ddl_test_t27; 29 Table Create Table 30 mmysql_ddl_test_t27 CREATE TABLE `mmysql_ddl_test_t27` (\n`id` INT DEFAULT NULL,\n`name` VARCHAR(255) DEFAULT NULL\n) 31 create table if not exists mmysql_ddl_test_t28(id int, name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci); 32 show create table mmysql_ddl_test_t28; 33 Table Create Table 34 mmysql_ddl_test_t28 CREATE TABLE `mmysql_ddl_test_t28` (\n`id` INT DEFAULT NULL,\n`name` VARCHAR(255) DEFAULT NULL\n) 35 create table if not exists mmysql_ddl_test_t29(id int, name varchar(255),primary key (id) USING BTREE); 36 show create table mmysql_ddl_test_t29; 37 Table Create Table 38 mmysql_ddl_test_t29 CREATE TABLE `mmysql_ddl_test_t29` (\n`id` INT NOT NULL,\n`name` VARCHAR(255) DEFAULT NULL,\nPRIMARY KEY (`id`)\n) 39 create table mmysql_ddl_test_t210 (id int); 40 LOCK TABLES t10 WRITE; 41 alter table mmysql_ddl_test_t210 add column class varchar(50); 42 UNLOCK TABLES; 43 show create table mmysql_ddl_test_t210; 44 Table Create Table 45 mmysql_ddl_test_t210 CREATE TABLE `mmysql_ddl_test_t210` (\n`id` INT DEFAULT NULL,\n`class` VARCHAR(50) DEFAULT NULL\n) 46 create table mmysql_ddl_test_t211 (id int AUTO_INCREMENT); 47 show create table mmysql_ddl_test_t211; 48 Table Create Table 49 mmysql_ddl_test_t211 CREATE TABLE `mmysql_ddl_test_t211` (\n`id` INT NOT NULL AUTO_INCREMENT\n) 50 create table mmysql_ddl_test_t212 (id int) AUTO_INCREMENT = 157472; 51 show create table mmysql_ddl_test_t212; 52 Table Create Table 53 mmysql_ddl_test_t212 CREATE TABLE `mmysql_ddl_test_t212` (\n`id` INT DEFAULT NULL\n) 54 create table mmysql_ddl_test_t213 (id int AUTO_INCREMENT) AUTO_INCREMENT = 157472; 55 show create table mmysql_ddl_test_t213; 56 Table Create Table 57 mmysql_ddl_test_t213 CREATE TABLE `mmysql_ddl_test_t213` (\n`id` INT NOT NULL AUTO_INCREMENT\n) 58 create table mmysql_ddl_test_t214 (id int) ROW_FORMAT=DYNAMIC; 59 show create table mmysql_ddl_test_t214; 60 Table Create Table 61 mmysql_ddl_test_t214 CREATE TABLE `mmysql_ddl_test_t214` (\n`id` INT DEFAULT NULL\n) 62 create table mmysql_ddl_test_t215 (id int) ROW_FORMAT = COMPACT; 63 show create table mmysql_ddl_test_t215; 64 Table Create Table 65 mmysql_ddl_test_t215 CREATE TABLE `mmysql_ddl_test_t215` (\n`id` INT DEFAULT NULL\n) 66 create table if not exists mmysql_ddl_test_t216(id int AUTO_INCREMENT, name varchar(255) CHARACTER SET utf8 COLLATE utf8mb4_general_ci,primary key (id) USING BTREE) AUTO_INCREMENT=123 engine = 'InnoDB' DEFAULT CHARSET=utf8 COLLATE = utf8mb4_general_ci ROW_FORMAT = COMPACT; 67 show create table mmysql_ddl_test_t216; 68 Table Create Table 69 mmysql_ddl_test_t216 CREATE TABLE `mmysql_ddl_test_t216` (\n`id` INT NOT NULL AUTO_INCREMENT,\n`name` VARCHAR(255) DEFAULT NULL,\nPRIMARY KEY (`id`)\n) 70 1. unre 71 DROP TABLE IF EXISTS `projects`; 72 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 2 near "1. unre 73 DROP TABLE IF EXISTS `projects`;"; 74 CREATE TABLE `projects` ( 75 `project_id` VARCHAR(64) NOT NULL, 76 `company_id` VARCHAR(64) NOT NULL, 77 `project_no` VARCHAR(255) DEFAULT null, 78 `project_name` VARCHAR(255) NOT NULL, 79 `project_type` VARCHAR(255) NOT NULL, 80 `project_unit` VARCHAR(255) DEFAULT null, 81 `project_leader` VARCHAR(255) NOT NULL, 82 `address` VARCHAR(255) DEFAULT null, 83 `number_of_building` INT DEFAULT null, 84 `project_state` VARCHAR(64) DEFAULT null, 85 `supervisor` VARCHAR(255) DEFAULT null, 86 `build_license` VARCHAR(64) DEFAULT null, 87 `ceiling_normal_plane_distance` VARCHAR(32) DEFAULT null COMMENT '顶板距法面距离', 88 `floor_normal_plane_distance` VARCHAR(32) DEFAULT null COMMENT '底板距法面距离', 89 `ext1` VARCHAR(1024) DEFAULT null COMMENT '扩展字段', 90 `ext2` VARCHAR(1024) DEFAULT null COMMENT '扩展字段', 91 `ext3` VARCHAR(1024) DEFAULT null COMMENT '扩展字段', 92 `sub_company_id` VARCHAR(64) DEFAULT null, 93 `parent_project_id` VARCHAR(64) DEFAULT null, 94 `distinguish_suite` INT DEFAULT '1' COMMENT '是否分户(0,否 1，是)', 95 `draw_switch` INT DEFAULT null, 96 `version` VARCHAR(64) DEFAULT null, 97 `created_by` VARCHAR(64) DEFAULT null, 98 `created_time` DATETIME DEFAULT null, 99 `updated_by` VARCHAR(64) DEFAULT null, 100 `updated_time` DATETIME DEFAULT null, 101 `is_deleted` INT DEFAULT null, 102 `deleted_by` VARCHAR(64) DEFAULT null, 103 `deleted_time` DATETIME DEFAULT null, 104 PRIMARY KEY (`project_id`), 105 UNIQUE KEY `idx_projects_project_id` (`project_id`), 106 KEY `idx_company_id` (`company_id`), 107 KEY `idx_project_state` (`project_state`), 108 KEY `idx_sub_company_id` (`sub_company_id`) 109 ); 110 desc `projects`; 111 Field Type Null Key Default Extra Comment 112 project_id VARCHAR(64) NO PRI null 113 company_id VARCHAR(64) NO MUL null 114 project_no VARCHAR(255) YES null 115 project_name VARCHAR(255) NO null 116 project_type VARCHAR(255) NO null 117 project_unit VARCHAR(255) YES null 118 project_leader VARCHAR(255) NO null 119 address VARCHAR(255) YES null 120 number_of_building INT(32) YES null 121 project_state VARCHAR(64) YES MUL null 122 supervisor VARCHAR(255) YES null 123 build_license VARCHAR(64) YES null 124 ceiling_normal_plane_distance VARCHAR(32) YES null 顶板距法面距离 125 floor_normal_plane_distance VARCHAR(32) YES null 底板距法面距离 126 ext1 VARCHAR(1024) YES null 扩展字段 127 ext2 VARCHAR(1024) YES null 扩展字段 128 ext3 VARCHAR(1024) YES null 扩展字段 129 sub_company_id VARCHAR(64) YES MUL null 130 parent_project_id VARCHAR(64) YES null 131 distinguish_suite INT(32) YES '1' 是否分户(0,否 1，是) 132 draw_switch INT(32) YES null 133 version VARCHAR(64) YES null 134 created_by VARCHAR(64) YES null 135 created_time DATETIME(0) YES null 136 updated_by VARCHAR(64) YES null 137 updated_time DATETIME(0) YES null 138 is_deleted INT(32) YES null 139 deleted_by VARCHAR(64) YES null 140 deleted_time DATETIME(0) YES null 141 ALTER TABLE `projects` 142 MODIFY COLUMN `build_license` varchar (64) NULL DEFAULT NULL AFTER `supervisor`, 143 MODIFY COLUMN `created_by` varchar(64) NULL DEFAULT NULL AFTER `distinguish_suite`, 144 MODIFY COLUMN `created_time` datetime (0) NULL DEFAULT NULL AFTER `created_by`, 145 MODIFY COLUMN `is_deleted` int (11) NULL DEFAULT NULL AFTER `updated_time`, 146 MODIFY COLUMN `deleted_by` varchar (64) NULL DEFAULT NULL AFTER `is_deleted`, 147 MODIFY COLUMN `deleted_time` datetime(0) NULL DEFAULT NULL AFTER `deleted_by`, 148 ADD COLUMN `draw_switch` int(11) NULL AFTER `distinguish_suite`, 149 ADD COLUMN `version` varchar (64) NULL AFTER `draw_switch`; 150 Duplicate column name 'draw_switch' 151 ALTER TABLE `projects` 152 MODIFY COLUMN `build_license` varchar (64) NULL DEFAULT NULL AFTER `supervisor`, 153 MODIFY COLUMN `created_by` varchar(64) NULL DEFAULT NULL AFTER `distinguish_suite`, 154 MODIFY COLUMN `created_time` datetime (0) NULL DEFAULT NULL AFTER `created_by`, 155 MODIFY COLUMN `is_deleted` int (11) NULL DEFAULT NULL AFTER `updated_time`, 156 MODIFY COLUMN `deleted_by` varchar (64) NULL DEFAULT NULL AFTER `is_deleted`, 157 MODIFY COLUMN `deleted_time` datetime(0) NULL DEFAULT NULL AFTER `deleted_by`, 158 ADD COLUMN `draw_switch2` int(11) NULL AFTER `distinguish_suite`, 159 ADD COLUMN `version2` varchar (64) NULL AFTER `draw_switch`; 160 desc `projects`; 161 Field Type Null Key Default Extra Comment 162 project_id VARCHAR(64) NO PRI null 163 company_id VARCHAR(64) NO MUL null 164 project_no VARCHAR(255) YES null 165 project_name VARCHAR(255) NO null 166 project_type VARCHAR(255) NO null 167 project_unit VARCHAR(255) YES null 168 project_leader VARCHAR(255) NO null 169 address VARCHAR(255) YES null 170 number_of_building INT(32) YES null 171 project_state VARCHAR(64) YES MUL null 172 supervisor VARCHAR(255) YES null 173 build_license VARCHAR(64) YES null 174 ceiling_normal_plane_distance VARCHAR(32) YES null 顶板距法面距离 175 floor_normal_plane_distance VARCHAR(32) YES null 底板距法面距离 176 ext1 VARCHAR(1024) YES null 扩展字段 177 ext2 VARCHAR(1024) YES null 扩展字段 178 ext3 VARCHAR(1024) YES null 扩展字段 179 sub_company_id VARCHAR(64) YES MUL null 180 parent_project_id VARCHAR(64) YES null 181 distinguish_suite INT(32) YES '1' 是否分户(0,否 1，是) 182 draw_switch2 INT(32) YES null 183 created_by VARCHAR(64) YES null 184 created_time DATETIME(0) YES null 185 draw_switch INT(32) YES null 186 version2 VARCHAR(64) YES null 187 version VARCHAR(64) YES null 188 updated_by VARCHAR(64) YES null 189 updated_time DATETIME(0) YES null 190 is_deleted INT(32) YES null 191 deleted_by VARCHAR(64) YES null 192 deleted_time DATETIME(0) YES null 193 drop database if exists mysql_ddl_test_db; 194 create database mysql_ddl_test_db; 195 use mysql_ddl_test_db; 196 CREATE TABLE table_basic_for_alter_100m ( 197 col1 TINYINT DEFAULT NULL, 198 col2 SMALLINT DEFAULT NULL, 199 col3 INT NOT NULL, 200 col4 BIGINT NOT NULL, 201 col5 TINYINT UNSIGNED DEFAULT NULL, 202 col6 SMALLINT UNSIGNED DEFAULT NULL, 203 col7 INT UNSIGNED DEFAULT NULL, 204 col8 BIGINT UNSIGNED DEFAULT NULL, 205 col9 FLOAT DEFAULT NULL, 206 col10 DOUBLE DEFAULT NULL, 207 col11 VARCHAR(255) DEFAULT NULL, 208 col12 DATE DEFAULT NULL, 209 col13 DATETIME DEFAULT NULL, 210 col14 TIMESTAMP DEFAULT NULL, 211 col15 BOOL DEFAULT NULL, 212 col16 DECIMAL(16,6) DEFAULT NULL, 213 col17 TEXT DEFAULT NULL, 214 col18 JSON DEFAULT NULL, 215 col19 BLOB DEFAULT NULL, 216 col20 BINARY(255) DEFAULT NULL, 217 col21 VARBINARY(255) DEFAULT NULL, 218 col22 VECF32(3) DEFAULT NULL, 219 col23 VECF32(3) DEFAULT NULL, 220 col24 VECF64(3) DEFAULT NULL, 221 col25 VECF64(3) DEFAULT NULL, 222 KEY col3_col4 (col3,col4), 223 UNIQUE KEY col4 (col4) 224 ) ; 225 desc table_basic_for_alter_100m; 226 Field Type Null Key Default Extra Comment 227 col1 TINYINT(8) YES null 228 col2 SMALLINT(16) YES null 229 col3 INT(32) NO MUL null 230 col4 BIGINT(64) NO PRI null 231 col5 TINYINT UNSIGNED(8) YES null 232 col6 SMALLINT UNSIGNED(16) YES null 233 col7 INT UNSIGNED(32) YES null 234 col8 BIGINT UNSIGNED(64) YES null 235 col9 FLOAT(0) YES null 236 col10 DOUBLE(0) YES null 237 col11 VARCHAR(255) YES null 238 col12 DATE(0) YES null 239 col13 DATETIME(0) YES null 240 col14 TIMESTAMP(0) YES null 241 col15 BOOL(0) YES null 242 col16 DECIMAL64(16) YES null 243 col17 TEXT(0) YES null 244 col18 JSON(0) YES null 245 col19 BLOB(0) YES null 246 col20 BINARY(255) YES null 247 col21 VARBINARY(255) YES null 248 col22 VECF32(3) YES null 249 col23 VECF32(3) YES null 250 col24 VECF64(3) YES null 251 col25 VECF64(3) YES null 252 CREATE TABLE user_table ( 253 id INT(11), 254 name VARCHAR(50), 255 UNIQUE KEY (id) 256 ); 257 desc user_table; 258 Field Type Null Key Default Extra Comment 259 id INT(32) YES UNI null 260 name VARCHAR(50) YES null 261 CREATE TABLE users ( 262 id INT(11) NOT NULL, 263 name VARCHAR(50), 264 UNIQUE KEY (id) 265 ); 266 desc users; 267 Field Type Null Key Default Extra Comment 268 id INT(32) NO PRI null 269 name VARCHAR(50) YES null 270 drop database if exists mysql_ddl_test_db; 271 drop database if exists db1; 272 create database db1; 273 use db1; 274 drop table if exists t1; 275 CREATE TABLE t1( 276 a INTEGER, 277 b CHAR(10), 278 c date, 279 d decimal(7,2), 280 UNIQUE KEY(a, b) 281 ); 282 desc t1; 283 Field Type Null Key Default Extra Comment 284 a INT(32) YES MUL null 285 b CHAR(10) YES null 286 c DATE(0) YES null 287 d DECIMAL64(7) YES null 288 drop table if exists t2; 289 CREATE TABLE t2( 290 col1 TINYINT DEFAULT NULL, 291 col2 SMALLINT DEFAULT NULL, 292 col3 INT NOT NULL, 293 col4 BIGINT NOT NULL, 294 KEY (col3,col4), 295 UNIQUE KEY (col4) 296 ) ; 297 desc t2; 298 Field Type Null Key Default Extra Comment 299 col1 TINYINT(8) YES null 300 col2 SMALLINT(16) YES null 301 col3 INT(32) NO MUL null 302 col4 BIGINT(64) NO PRI null 303 drop table if exists t3; 304 CREATE TABLE t3( 305 a INTEGER, 306 b CHAR(10), 307 c date, 308 d decimal(7,2), 309 KEY (c, d), 310 UNIQUE KEY (d) 311 ); 312 desc t3; 313 Field Type Null Key Default Extra Comment 314 a INT(32) YES null 315 b CHAR(10) YES null 316 c DATE(0) YES MUL null 317 d DECIMAL64(7) YES UNI null 318 drop table if exists t4; 319 CREATE TABLE t4( 320 a INTEGER, 321 b CHAR(10), 322 c date, 323 d decimal(7,2), 324 KEY (a, b), 325 UNIQUE KEY (a) 326 ); 327 desc t4; 328 Field Type Null Key Default Extra Comment 329 a INT(32) YES UNI null 330 b CHAR(10) YES null 331 c DATE(0) YES null 332 d DECIMAL64(7) YES null 333 drop database if exists db1;