github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/mysql_ddl_2.sql (about) 1 -- testcase_2. 2 -- ENGINE=xxx (InnoDB, MyISAM, ...) 3 -- DEFAULT CHARSET=xxx (latin1, utf8, ...) COLLATE=utf8mb4_general_ci 4 -- CHARACTER SET xxx (utf8, ...) COLLATE utf8mb4_general_ci 5 -- USING xxx (BTREE, HASH, ...) 6 -- UNLOCK TABLES and LOCK TABLES 7 -- AUTO_INCREMENT=xxx, AUTO_INCREMENT xxx 8 -- ROW_FORMAT=xxx (COMPACT) 9 -- Combination 10 11 create database if not exists mysql_ddl_test_db; 12 use mysql_ddl_test_db; 13 14 create table if not exists mmysql_ddl_test_t21(id int, name varchar(255)) engine = 'InnoDB'; 15 show create table mmysql_ddl_test_t21; 16 17 create table if not exists mmysql_ddl_test_t22(id int, name varchar(255)) DEFAULT CHARSET=utf8 COLLATE = utf8mb4_general_ci ; 18 show create table mmysql_ddl_test_t22; 19 20 create table if not exists mmysql_ddl_test_t23(id int, name varchar(255)) DEFAULT CHARSET = utf8; 21 show create table mmysql_ddl_test_t23; 22 23 create table if not exists mmysql_ddl_test_t24(id int, name varchar(255)) DEFAULT CHARSET= utf8; 24 show create table mmysql_ddl_test_t24; 25 26 create table if not exists mmysql_ddl_test_t25(id int, name varchar(255)) DEFAULT CHARSET =utf8; 27 show create table mmysql_ddl_test_t25; 28 29 create table if not exists mmysql_ddl_test_t26(id int, name varchar(255)) DEFAULT CHARSET = utf8 COLLATE=utf8mb4_general_ci ; 30 show create table mmysql_ddl_test_t26; 31 32 create table if not exists mmysql_ddl_test_t27(id int, name varchar(255) CHARACTER SET utf8 COLLATE utf8mb4_general_ci); 33 show create table mmysql_ddl_test_t27; 34 35 create table if not exists mmysql_ddl_test_t28(id int, name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci); 36 show create table mmysql_ddl_test_t28; 37 38 create table if not exists mmysql_ddl_test_t29(id int, name varchar(255),primary key (id) USING BTREE); 39 show create table mmysql_ddl_test_t29; 40 41 create table mmysql_ddl_test_t210 (id int); 42 LOCK TABLES t10 WRITE; 43 alter table mmysql_ddl_test_t210 add column class varchar(50); 44 UNLOCK TABLES; 45 show create table mmysql_ddl_test_t210; 46 47 create table mmysql_ddl_test_t211 (id int AUTO_INCREMENT); 48 show create table mmysql_ddl_test_t211; 49 50 create table mmysql_ddl_test_t212 (id int) AUTO_INCREMENT = 157472; 51 show create table mmysql_ddl_test_t212; 52 53 create table mmysql_ddl_test_t213 (id int AUTO_INCREMENT) AUTO_INCREMENT = 157472; 54 show create table mmysql_ddl_test_t213; 55 56 create table mmysql_ddl_test_t214 (id int) ROW_FORMAT=DYNAMIC; 57 show create table mmysql_ddl_test_t214; 58 59 create table mmysql_ddl_test_t215 (id int) ROW_FORMAT = COMPACT; 60 show create table mmysql_ddl_test_t215; 61 62 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; 63 show create table mmysql_ddl_test_t216; 64 65 1. unre 66 -- create table 67 DROP TABLE IF EXISTS `projects`; 68 CREATE TABLE `projects` ( 69 `project_id` VARCHAR(64) NOT NULL, 70 `company_id` VARCHAR(64) NOT NULL, 71 `project_no` VARCHAR(255) DEFAULT null, 72 `project_name` VARCHAR(255) NOT NULL, 73 `project_type` VARCHAR(255) NOT NULL, 74 `project_unit` VARCHAR(255) DEFAULT null, 75 `project_leader` VARCHAR(255) NOT NULL, 76 `address` VARCHAR(255) DEFAULT null, 77 `number_of_building` INT DEFAULT null, 78 `project_state` VARCHAR(64) DEFAULT null, 79 `supervisor` VARCHAR(255) DEFAULT null, 80 `build_license` VARCHAR(64) DEFAULT null, 81 `ceiling_normal_plane_distance` VARCHAR(32) DEFAULT null COMMENT '顶板距法面距离', 82 `floor_normal_plane_distance` VARCHAR(32) DEFAULT null COMMENT '底板距法面距离', 83 `ext1` VARCHAR(1024) DEFAULT null COMMENT '扩展字段', 84 `ext2` VARCHAR(1024) DEFAULT null COMMENT '扩展字段', 85 `ext3` VARCHAR(1024) DEFAULT null COMMENT '扩展字段', 86 `sub_company_id` VARCHAR(64) DEFAULT null, 87 `parent_project_id` VARCHAR(64) DEFAULT null, 88 `distinguish_suite` INT DEFAULT '1' COMMENT '是否分户(0,否 1，是)', 89 `draw_switch` INT DEFAULT null, 90 `version` VARCHAR(64) DEFAULT null, 91 `created_by` VARCHAR(64) DEFAULT null, 92 `created_time` DATETIME DEFAULT null, 93 `updated_by` VARCHAR(64) DEFAULT null, 94 `updated_time` DATETIME DEFAULT null, 95 `is_deleted` INT DEFAULT null, 96 `deleted_by` VARCHAR(64) DEFAULT null, 97 `deleted_time` DATETIME DEFAULT null, 98 PRIMARY KEY (`project_id`), 99 UNIQUE KEY `idx_projects_project_id` (`project_id`), 100 KEY `idx_company_id` (`company_id`), 101 KEY `idx_project_state` (`project_state`), 102 KEY `idx_sub_company_id` (`sub_company_id`) 103 ); 104 105 -- check table definition 106 desc `projects`; 107 108 -- change table definition 109 ALTER TABLE `projects` 110 MODIFY COLUMN `build_license` varchar (64) NULL DEFAULT NULL AFTER `supervisor`, 111 MODIFY COLUMN `created_by` varchar(64) NULL DEFAULT NULL AFTER `distinguish_suite`, 112 MODIFY COLUMN `created_time` datetime (0) NULL DEFAULT NULL AFTER `created_by`, 113 MODIFY COLUMN `is_deleted` int (11) NULL DEFAULT NULL AFTER `updated_time`, 114 MODIFY COLUMN `deleted_by` varchar (64) NULL DEFAULT NULL AFTER `is_deleted`, 115 MODIFY COLUMN `deleted_time` datetime(0) NULL DEFAULT NULL AFTER `deleted_by`, 116 ADD COLUMN `draw_switch` int(11) NULL AFTER `distinguish_suite`, 117 ADD COLUMN `version` varchar (64) NULL AFTER `draw_switch`; 118 119 -- change table definition 120 ALTER TABLE `projects` 121 MODIFY COLUMN `build_license` varchar (64) NULL DEFAULT NULL AFTER `supervisor`, 122 MODIFY COLUMN `created_by` varchar(64) NULL DEFAULT NULL AFTER `distinguish_suite`, 123 MODIFY COLUMN `created_time` datetime (0) NULL DEFAULT NULL AFTER `created_by`, 124 MODIFY COLUMN `is_deleted` int (11) NULL DEFAULT NULL AFTER `updated_time`, 125 MODIFY COLUMN `deleted_by` varchar (64) NULL DEFAULT NULL AFTER `is_deleted`, 126 MODIFY COLUMN `deleted_time` datetime(0) NULL DEFAULT NULL AFTER `deleted_by`, 127 ADD COLUMN `draw_switch2` int(11) NULL AFTER `distinguish_suite`, 128 ADD COLUMN `version2` varchar (64) NULL AFTER `draw_switch`; 129 -- check table definition 130 desc `projects`; 131 132 drop database if exists mysql_ddl_test_db; 133 134 create database mysql_ddl_test_db; 135 use mysql_ddl_test_db; 136 CREATE TABLE table_basic_for_alter_100m ( 137 col1 TINYINT DEFAULT NULL, 138 col2 SMALLINT DEFAULT NULL, 139 col3 INT NOT NULL, 140 col4 BIGINT NOT NULL, 141 col5 TINYINT UNSIGNED DEFAULT NULL, 142 col6 SMALLINT UNSIGNED DEFAULT NULL, 143 col7 INT UNSIGNED DEFAULT NULL, 144 col8 BIGINT UNSIGNED DEFAULT NULL, 145 col9 FLOAT DEFAULT NULL, 146 col10 DOUBLE DEFAULT NULL, 147 col11 VARCHAR(255) DEFAULT NULL, 148 col12 DATE DEFAULT NULL, 149 col13 DATETIME DEFAULT NULL, 150 col14 TIMESTAMP DEFAULT NULL, 151 col15 BOOL DEFAULT NULL, 152 col16 DECIMAL(16,6) DEFAULT NULL, 153 col17 TEXT DEFAULT NULL, 154 col18 JSON DEFAULT NULL, 155 col19 BLOB DEFAULT NULL, 156 col20 BINARY(255) DEFAULT NULL, 157 col21 VARBINARY(255) DEFAULT NULL, 158 col22 VECF32(3) DEFAULT NULL, 159 col23 VECF32(3) DEFAULT NULL, 160 col24 VECF64(3) DEFAULT NULL, 161 col25 VECF64(3) DEFAULT NULL, 162 KEY col3_col4 (col3,col4), 163 UNIQUE KEY col4 (col4) 164 ) ; 165 desc table_basic_for_alter_100m; 166 167 CREATE TABLE user_table ( 168 id INT(11), 169 name VARCHAR(50), 170 UNIQUE KEY (id) 171 ); 172 desc user_table; 173 174 CREATE TABLE users ( 175 id INT(11) NOT NULL, 176 name VARCHAR(50), 177 UNIQUE KEY (id) 178 ); 179 desc users; 180 181 drop database if exists mysql_ddl_test_db; 182 183 drop database if exists db1; 184 create database db1; 185 use db1; 186 drop table if exists t1; 187 CREATE TABLE t1( 188 a INTEGER, 189 b CHAR(10), 190 c date, 191 d decimal(7,2), 192 UNIQUE KEY(a, b) 193 ); 194 desc t1; 195 196 drop table if exists t2; 197 CREATE TABLE t2( 198 col1 TINYINT DEFAULT NULL, 199 col2 SMALLINT DEFAULT NULL, 200 col3 INT NOT NULL, 201 col4 BIGINT NOT NULL, 202 KEY (col3,col4), 203 UNIQUE KEY (col4) 204 ) ; 205 desc t2; 206 207 drop table if exists t3; 208 CREATE TABLE t3( 209 a INTEGER, 210 b CHAR(10), 211 c date, 212 d decimal(7,2), 213 KEY (c, d), 214 UNIQUE KEY (d) 215 ); 216 desc t3; 217 218 drop table if exists t4; 219 CREATE TABLE t4( 220 a INTEGER, 221 b CHAR(10), 222 c date, 223 d decimal(7,2), 224 KEY (a, b), 225 UNIQUE KEY (a) 226 ); 227 desc t4; 228 229 drop database if exists db1;