github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/alter_table_modify_column.result (about) 1 drop database if exists db1; 2 create database db1; 3 use db1; 4 drop table if exists t1; 5 CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10)); 6 desc t1; 7 Field Type Null Key Default Extra Comment 8 a INT(32) NO PRI null 9 b CHAR(10) YES null 10 insert into t1 values(1, 'ab'); 11 insert into t1 values(2, 'ac'); 12 insert into t1 values(3, 'ad'); 13 select * from t1; 14 a b 15 1 ab 16 2 ac 17 3 ad 18 alter table t1 modify a VARCHAR(20); 19 desc t1; 20 Field Type Null Key Default Extra Comment 21 a VARCHAR(20) NO PRI null 22 b CHAR(10) YES null 23 select * from t1; 24 a b 25 1 ab 26 2 ac 27 3 ad 28 drop table if exists t1; 29 CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10), c date); 30 desc t1; 31 Field Type Null Key Default Extra Comment 32 a INT(32) NO PRI null 33 b CHAR(10) YES null 34 c DATE(0) YES null 35 insert into t1 values(1, 'ab', '1980-12-17'); 36 insert into t1 values(2, 'ac', '1981-02-20'); 37 insert into t1 values(3, 'ad', '1981-02-22'); 38 select * from t1; 39 a b c 40 1 ab 1980-12-17 41 2 ac 1981-02-20 42 3 ad 1981-02-22 43 alter table t1 modify a VARCHAR(20) after b; 44 desc t1; 45 Field Type Null Key Default Extra Comment 46 b CHAR(10) YES null 47 a VARCHAR(20) NO PRI null 48 c DATE(0) YES null 49 select * from t1; 50 b a c 51 ab 1 1980-12-17 52 ac 2 1981-02-20 53 ad 3 1981-02-22 54 drop table if exists t1; 55 CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10), c date); 56 desc t1; 57 Field Type Null Key Default Extra Comment 58 a INT(32) NO PRI null 59 b CHAR(10) YES null 60 c DATE(0) YES null 61 insert into t1 values(1, 'ab', '1980-12-17'); 62 insert into t1 values(2, 'ac', '1981-02-20'); 63 insert into t1 values(3, 'ad', '1981-02-22'); 64 select * from t1; 65 a b c 66 1 ab 1980-12-17 67 2 ac 1981-02-20 68 3 ad 1981-02-22 69 alter table t1 modify a VARCHAR(20) after c; 70 desc t1; 71 Field Type Null Key Default Extra Comment 72 b CHAR(10) YES null 73 c DATE(0) YES null 74 a VARCHAR(20) NO PRI null 75 select * from t1; 76 b c a 77 ab 1980-12-17 1 78 ac 1981-02-20 2 79 ad 1981-02-22 3 80 drop table if exists t1; 81 CREATE TABLE t1 (a INTEGER, b CHAR(10), c date, PRIMARY KEY(a)); 82 desc t1; 83 Field Type Null Key Default Extra Comment 84 a INT(32) NO PRI null 85 b CHAR(10) YES null 86 c DATE(0) YES null 87 insert into t1 values(1, 'ab', '1980-12-17'); 88 insert into t1 values(2, 'ac', '1981-02-20'); 89 insert into t1 values(3, 'ad', '1981-02-22'); 90 select * from t1; 91 a b c 92 1 ab 1980-12-17 93 2 ac 1981-02-20 94 3 ad 1981-02-22 95 alter table t1 modify b VARCHAR(20) first; 96 desc t1; 97 Field Type Null Key Default Extra Comment 98 b VARCHAR(20) YES null 99 a INT(32) NO PRI null 100 c DATE(0) YES null 101 select * from t1; 102 b a c 103 ab 1 1980-12-17 104 ac 2 1981-02-20 105 ad 3 1981-02-22 106 drop table if exists t1; 107 CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2)); 108 desc t1; 109 Field Type Null Key Default Extra Comment 110 a INT(32) NO PRI null 111 b CHAR(10) YES null 112 c DATE(0) YES null 113 d DECIMAL64(7) YES null 114 insert into t1 values(1, 'ab', '1980-12-17', 800); 115 insert into t1 values(2, 'ac', '1981-02-20', 1600); 116 insert into t1 values(3, 'ad', '1981-02-22', 500); 117 select * from t1; 118 a b c d 119 1 ab 1980-12-17 800.00 120 2 ac 1981-02-20 1600.00 121 3 ad 1981-02-22 500.00 122 alter table t1 modify b VARCHAR(20), modify d int unsigned; 123 desc t1; 124 Field Type Null Key Default Extra Comment 125 a INT(32) NO PRI null 126 b VARCHAR(20) YES null 127 c DATE(0) YES null 128 d INT UNSIGNED(32) YES null 129 select * from t1; 130 a b c d 131 1 ab 1980-12-17 800 132 2 ac 1981-02-20 1600 133 3 ad 1981-02-22 500 134 drop table if exists t1; 135 CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2)); 136 desc t1; 137 Field Type Null Key Default Extra Comment 138 a INT(32) NO PRI null 139 b CHAR(10) YES null 140 c DATE(0) YES null 141 d DECIMAL64(7) YES null 142 insert into t1 values(1, 'ab', '1980-12-17', 800); 143 insert into t1 values(2, 'ac', '1981-02-20', 1600); 144 insert into t1 values(3, 'ad', '1981-02-22', 500); 145 select * from t1; 146 a b c d 147 1 ab 1980-12-17 800.00 148 2 ac 1981-02-20 1600.00 149 3 ad 1981-02-22 500.00 150 alter table t1 modify a VARCHAR(20) PRIMARY KEY; 151 Multiple primary key defined 152 alter table t1 modify b VARCHAR(20) PRIMARY KEY; 153 Multiple primary key defined 154 alter table t1 modify b VARCHAR(20) first, modify d int unsigned after b; 155 desc t1; 156 Field Type Null Key Default Extra Comment 157 b VARCHAR(20) YES null 158 d INT UNSIGNED(32) YES null 159 a INT(32) NO PRI null 160 c DATE(0) YES null 161 select * from t1; 162 b d a c 163 ab 800 1 1980-12-17 164 ac 1600 2 1981-02-20 165 ad 500 3 1981-02-22 166 drop table if exists t1; 167 CREATE TABLE t1(a INTEGER, b CHAR(10), c datetime, d decimal(7,2), PRIMARY KEY(a, b)); 168 desc t1; 169 Field Type Null Key Default Extra Comment 170 a INT(32) NO PRI null 171 b CHAR(10) NO PRI null 172 c DATETIME(0) YES null 173 d DECIMAL64(7) YES null 174 insert into t1 values(1, 'ab', '1980-12-17 11:34:45', 800); 175 insert into t1 values(2, 'ac', '1981-02-20 10:34:45', 1600); 176 insert into t1 values(3, 'ad', '1981-02-22 09:34:45', 500); 177 select * from t1; 178 a b c d 179 1 ab 1980-12-17 11:34:45 800.00 180 2 ac 1981-02-20 10:34:45 1600.00 181 3 ad 1981-02-22 09:34:45 500.00 182 alter table t1 modify c datetime default '2023-06-21 12:34:45' on update CURRENT_TIMESTAMP; 183 desc t1; 184 Field Type Null Key Default Extra Comment 185 a INT(32) NO PRI null 186 b CHAR(10) NO PRI null 187 c DATETIME(0) YES '2023-06-21 12:34:45' 188 d DECIMAL64(7) YES null 189 select * from t1; 190 a b c d 191 1 ab 1980-12-17 11:34:45 800.00 192 2 ac 1981-02-20 10:34:45 1600.00 193 3 ad 1981-02-22 09:34:45 500.00 194 alter table t1 modify c date; 195 desc t1; 196 Field Type Null Key Default Extra Comment 197 a INT(32) NO PRI null 198 b CHAR(10) NO PRI null 199 c DATE(0) YES null 200 d DECIMAL64(7) YES null 201 select * from t1; 202 a b c d 203 1 ab 1980-12-17 800.00 204 2 ac 1981-02-20 1600.00 205 3 ad 1981-02-22 500.00 206 drop table if exists t1; 207 CREATE TABLE t1 (a INTEGER, b CHAR(10), c datetime PRIMARY KEY default '2023-06-21' on update CURRENT_TIMESTAMP); 208 desc t1; 209 Field Type Null Key Default Extra Comment 210 a INT(32) YES null 211 b CHAR(10) YES null 212 c DATETIME(0) NO PRI '2023-06-21' 213 insert into t1 values(1, 'ab', '1980-12-17'); 214 insert into t1 values(2, 'ac', '1981-02-20'); 215 insert into t1 values(3, 'ad', '1981-02-22'); 216 select * from t1; 217 a b c 218 1 ab 1980-12-17 00:00:00 219 2 ac 1981-02-20 00:00:00 220 3 ad 1981-02-22 00:00:00 221 alter table t1 modify c date first; 222 desc t1; 223 Field Type Null Key Default Extra Comment 224 c DATE(0) NO PRI null 225 a INT(32) YES null 226 b CHAR(10) YES null 227 select * from t1; 228 c a b 229 1980-12-17 1 ab 230 1981-02-20 2 ac 231 1981-02-22 3 ad 232 alter table t1 modify c datetime default '2023-06-21'; 233 desc t1; 234 Field Type Null Key Default Extra Comment 235 c DATETIME(0) NO PRI '2023-06-21' 236 a INT(32) YES null 237 b CHAR(10) YES null 238 select * from t1; 239 c a b 240 1980-12-17 00:00:00 1 ab 241 1981-02-20 00:00:00 2 ac 242 1981-02-22 00:00:00 3 ad 243 drop table if exists t1; 244 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2), UNIQUE KEY(a, b)); 245 desc t1; 246 Field Type Null Key Default Extra Comment 247 a INT(32) NO PRI null 248 b CHAR(10) YES null 249 c DATE(0) YES null 250 d DECIMAL64(7) YES null 251 insert into t1 values(1, 'ab', '1980-12-17', 800); 252 insert into t1 values(2, 'ac', '1981-02-20', 1600); 253 insert into t1 values(3, 'ad', '1981-02-22', 500); 254 select * from t1; 255 a b c d 256 1 ab 1980-12-17 800.00 257 2 ac 1981-02-20 1600.00 258 3 ad 1981-02-22 500.00 259 alter table t1 modify b VARCHAR(20); 260 show index from t1; 261 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 262 t1 0 a 1 a A 0 NULL NULL YES NULL 263 t1 0 a 2 b A 0 NULL NULL YES YES NULL 264 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 265 alter table t1 modify b VARCHAR(20) UNIQUE KEY; 266 show index from t1; 267 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 268 t1 0 a 1 a A 0 NULL NULL YES NULL 269 t1 0 a 2 b A 0 NULL NULL YES YES NULL 270 t1 0 b 1 b A 0 NULL NULL YES YES NULL 271 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 272 alter table t1 modify b VARCHAR(20) UNIQUE KEY; 273 show index from t1; 274 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 275 t1 0 a 1 a A 0 NULL NULL YES NULL 276 t1 0 a 2 b A 0 NULL NULL YES YES NULL 277 t1 0 b 1 b A 0 NULL NULL YES YES NULL 278 t1 0 b_2 1 b A 0 NULL NULL YES YES NULL 279 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 280 desc t1; 281 Field Type Null Key Default Extra Comment 282 a INT(32) NO PRI null 283 b VARCHAR(20) YES UNI null 284 c DATE(0) YES null 285 d DECIMAL64(7) YES null 286 select * from t1; 287 a b c d 288 1 ab 1980-12-17 800.00 289 2 ac 1981-02-20 1600.00 290 3 ad 1981-02-22 500.00 291 drop table if exists t1; 292 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2), INDEX(a, b), KEY(c)); 293 desc t1; 294 Field Type Null Key Default Extra Comment 295 a INT(32) NO PRI null 296 b CHAR(10) YES null 297 c DATE(0) YES MUL null 298 d DECIMAL64(7) YES null 299 insert into t1 values(1, 'ab', '1980-12-17', 800); 300 insert into t1 values(2, 'ac', '1981-02-20', 1600); 301 insert into t1 values(3, 'ad', '1981-02-22', 500); 302 select * from t1; 303 a b c d 304 1 ab 1980-12-17 800.00 305 2 ac 1981-02-20 1600.00 306 3 ad 1981-02-22 500.00 307 alter table t1 modify b VARCHAR(20); 308 show index from t1; 309 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 310 t1 1 a 1 a A 0 NULL NULL YES NULL 311 t1 1 a 2 b A 0 NULL NULL YES YES NULL 312 t1 1 c 1 c A 0 NULL NULL YES YES NULL 313 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 314 alter table t1 modify b VARCHAR(20) KEY; 315 Multiple primary key defined 316 alter table t1 modify b VARCHAR(20) UNIQUE KEY; 317 show index from t1; 318 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 319 t1 0 b 1 b A 0 NULL NULL YES YES NULL 320 t1 1 a 1 a A 0 NULL NULL YES NULL 321 t1 1 a 2 b A 0 NULL NULL YES YES NULL 322 t1 1 c 1 c A 0 NULL NULL YES YES NULL 323 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 324 alter table t1 modify c VARCHAR(20) UNIQUE KEY; 325 show index from t1; 326 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 327 t1 0 b 1 b A 0 NULL NULL YES YES NULL 328 t1 0 c_2 1 c A 0 NULL NULL YES YES NULL 329 t1 1 a 1 a A 0 NULL NULL YES NULL 330 t1 1 a 2 b A 0 NULL NULL YES YES NULL 331 t1 1 c 1 c A 0 NULL NULL YES YES NULL 332 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 333 desc t1; 334 Field Type Null Key Default Extra Comment 335 a INT(32) NO PRI null 336 b VARCHAR(20) YES UNI null 337 c VARCHAR(20) YES UNI null 338 d DECIMAL64(7) YES null 339 select * from t1; 340 a b c d 341 1 ab 1980-12-17 800.00 342 2 ac 1981-02-20 1600.00 343 3 ad 1981-02-22 500.00 344 drop table if exists t1; 345 create table t1( 346 empno int unsigned auto_increment, 347 ename varchar(15) , 348 job varchar(10), 349 mgr int unsigned, 350 hiredate date, 351 sal decimal(7,2), 352 comm decimal(7,2), 353 deptno int unsigned, 354 primary key(empno, ename) 355 ); 356 desc t1; 357 Field Type Null Key Default Extra Comment 358 empno INT UNSIGNED(32) NO PRI null 359 ename VARCHAR(15) NO PRI null 360 job VARCHAR(10) YES null 361 mgr INT UNSIGNED(32) YES null 362 hiredate DATE(0) YES null 363 sal DECIMAL64(7) YES null 364 comm DECIMAL64(7) YES null 365 deptno INT UNSIGNED(32) YES null 366 INSERT INTO t1 VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 367 INSERT INTO t1 VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 368 INSERT INTO t1 VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 369 INSERT INTO t1 VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 370 INSERT INTO t1 VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 371 INSERT INTO t1 VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 372 INSERT INTO t1 VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 373 INSERT INTO t1 VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 374 INSERT INTO t1 VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 375 INSERT INTO t1 VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 376 INSERT INTO t1 VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 377 INSERT INTO t1 VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 378 INSERT INTO t1 VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 379 INSERT INTO t1 VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 380 select * from t1; 381 empno ename job mgr hiredate sal comm deptno 382 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 383 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 384 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 385 7566 JONES MANAGER 7839 1981-04-02 2975.00 null 20 386 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 387 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 null 30 388 7782 CLARK MANAGER 7839 1981-06-09 2450.00 null 10 389 7788 SCOTT ANALYST 7566 0087-07-13 3000.00 null 20 390 7839 KING PRESIDENT null 1981-11-17 5000.00 null 10 391 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 392 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 393 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 394 7902 FORD ANALYST 7566 1981-12-03 3000.00 null 20 395 7934 MILLER CLERK 7782 1982-01-23 1300.00 null 10 396 alter table t1 modify empno varchar(20) after sal; 397 desc t1; 398 Field Type Null Key Default Extra Comment 399 ename VARCHAR(15) NO PRI null 400 job VARCHAR(10) YES null 401 mgr INT UNSIGNED(32) YES null 402 hiredate DATE(0) YES null 403 sal DECIMAL64(7) YES null 404 empno VARCHAR(20) NO PRI null 405 comm DECIMAL64(7) YES null 406 deptno INT UNSIGNED(32) YES null 407 select * from t1; 408 ename job mgr hiredate sal empno comm deptno 409 SMITH CLERK 7902 1980-12-17 800.00 7369 null 20 410 ALLEN SALESMAN 7698 1981-02-20 1600.00 7499 300.00 30 411 WARD SALESMAN 7698 1981-02-22 1250.00 7521 500.00 30 412 JONES MANAGER 7839 1981-04-02 2975.00 7566 null 20 413 MARTIN SALESMAN 7698 1981-09-28 1250.00 7654 1400.00 30 414 BLAKE MANAGER 7839 1981-05-01 2850.00 7698 null 30 415 CLARK MANAGER 7839 1981-06-09 2450.00 7782 null 10 416 SCOTT ANALYST 7566 0087-07-13 3000.00 7788 null 20 417 KING PRESIDENT null 1981-11-17 5000.00 7839 null 10 418 TURNER SALESMAN 7698 1981-09-08 1500.00 7844 0.00 30 419 ADAMS CLERK 7788 0087-07-13 1100.00 7876 null 20 420 JAMES CLERK 7698 1981-12-03 950.00 7900 null 30 421 FORD ANALYST 7566 1981-12-03 3000.00 7902 null 20 422 MILLER CLERK 7782 1982-01-23 1300.00 7934 null 10 423 drop table if exists t1; 424 create table t1(a int unsigned, b varchar(15) NOT NULL, c date, d decimal(7,2), primary key(a)); 425 desc t1; 426 Field Type Null Key Default Extra Comment 427 a INT UNSIGNED(32) NO PRI null 428 b VARCHAR(15) NO null 429 c DATE(0) YES null 430 d DECIMAL64(7) YES null 431 insert into t1 values (7369,'SMITH','1980-12-17',800); 432 insert into t1 values (7499,'ALLEN','1981-02-20',1600); 433 insert into t1 values (7521,'WARD','1981-02-22',1250); 434 insert into t1 values (7566,'JONES','1981-04-02',2975); 435 insert into t1 values (7654,'MARTIN','1981-09-28',1250); 436 select * from t1; 437 a b c d 438 7369 SMITH 1980-12-17 800.00 439 7499 ALLEN 1981-02-20 1600.00 440 7521 WARD 1981-02-22 1250.00 441 7566 JONES 1981-04-02 2975.00 442 7654 MARTIN 1981-09-28 1250.00 443 alter table t1 modify a int auto_increment; 444 desc t1; 445 Field Type Null Key Default Extra Comment 446 a INT(32) NO PRI null 447 b VARCHAR(15) NO null 448 c DATE(0) YES null 449 d DECIMAL64(7) YES null 450 select * from t1; 451 a b c d 452 7369 SMITH 1980-12-17 800.00 453 7499 ALLEN 1981-02-20 1600.00 454 7521 WARD 1981-02-22 1250.00 455 7566 JONES 1981-04-02 2975.00 456 7654 MARTIN 1981-09-28 1250.00 457 alter table t1 modify d decimal(6,2); 458 desc t1; 459 Field Type Null Key Default Extra Comment 460 a INT(32) NO PRI null 461 b VARCHAR(15) NO null 462 c DATE(0) YES null 463 d DECIMAL64(6) YES null 464 select * from t1; 465 a b c d 466 7369 SMITH 1980-12-17 800.00 467 7499 ALLEN 1981-02-20 1600.00 468 7521 WARD 1981-02-22 1250.00 469 7566 JONES 1981-04-02 2975.00 470 7654 MARTIN 1981-09-28 1250.00 471 drop table if exists dept; 472 create table dept( 473 deptno varchar(20), 474 dname varchar(15), 475 loc varchar(50), 476 primary key(deptno) 477 ); 478 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 479 INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 480 INSERT INTO dept VALUES (30,'SALES','CHICAGO'); 481 INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON'); 482 drop table if exists emp; 483 create table emp( 484 empno int unsigned auto_increment, 485 ename varchar(15), 486 job varchar(10), 487 mgr int unsigned, 488 hiredate date, 489 sal decimal(7,2), 490 comm decimal(7,2), 491 deptno varchar(20), 492 primary key(empno), 493 constraint `c1` FOREIGN KEY (deptno) REFERENCES dept(deptno) 494 ); 495 INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 496 INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 497 INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 498 INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 499 INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 500 INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 501 INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 502 INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 503 INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 504 INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 505 INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 506 INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 507 INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 508 INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 509 alter table emp modify deptno char(20); 510 Cannot change column 'deptno': used in a foreign key constraint 'c1' 511 alter table emp modify deptno int; 512 Cannot change column 'deptno': used in a foreign key constraint 'c1' 513 alter table emp modify deptno varchar(10); 514 Cannot change column 'deptno': used in a foreign key constraint 'c1' 515 alter table emp modify deptno varchar(25); 516 desc emp; 517 Field Type Null Key Default Extra Comment 518 empno INT UNSIGNED(32) NO PRI null 519 ename VARCHAR(15) YES null 520 job VARCHAR(10) YES null 521 mgr INT UNSIGNED(32) YES null 522 hiredate DATE(0) YES null 523 sal DECIMAL64(7) YES null 524 comm DECIMAL64(7) YES null 525 deptno VARCHAR(25) YES MUL null 526 select * from emp; 527 empno ename job mgr hiredate sal comm deptno 528 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 529 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 530 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 531 7566 JONES MANAGER 7839 1981-04-02 2975.00 null 20 532 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 533 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 null 30 534 7782 CLARK MANAGER 7839 1981-06-09 2450.00 null 10 535 7788 SCOTT ANALYST 7566 0087-07-13 3000.00 null 20 536 7839 KING PRESIDENT null 1981-11-17 5000.00 null 10 537 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 538 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 539 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 540 7902 FORD ANALYST 7566 1981-12-03 3000.00 null 20 541 7934 MILLER CLERK 7782 1982-01-23 1300.00 null 10 542 drop table emp; 543 drop table dept; 544 drop table if exists t1; 545 CREATE TABLE t1(col1 int not null, col2 varchar(10)); 546 insert into t1 values (1, '137iu2'); 547 insert into t1 values (1, '73ujf34f'); 548 select * from t1; 549 col1 col2 550 1 137iu2 551 1 73ujf34f 552 alter table t1 modify col1 int primary key; 553 Duplicate entry '1' for key 'col1' 554 desc t1; 555 Field Type Null Key Default Extra Comment 556 col1 INT(32) NO null 557 col2 VARCHAR(10) YES null 558 alter table t1 modify col2 varchar(10) primary key; 559 desc t1; 560 Field Type Null Key Default Extra Comment 561 col1 INT(32) NO null 562 col2 VARCHAR(10) NO PRI null 563 insert into t1 values (1, 'cdsdsa'); 564 select * from t1; 565 col1 col2 566 1 137iu2 567 1 73ujf34f 568 1 cdsdsa 569 drop table t1; 570 drop database if exists db1; 571 drop database if exists test; 572 create database test; 573 use test; 574 drop table if exists bool01; 575 create table bool01 (col1 int, col2 char); 576 insert into bool01 values (1, 'q'); 577 insert into bool01 values (2, 'a'); 578 insert into bool01 values (10, '3'); 579 select * from bool01; 580 col1 col2 581 1 q 582 2 a 583 10 3 584 alter table bool01 modify col2 bool after col1; 585 invalid input: 'q' is not a valid bool expression 586 show create table bool01; 587 Table Create Table 588 bool01 CREATE TABLE `bool01` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(1) DEFAULT NULL\n) 589 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'bool01' and COLUMN_NAME not like '__mo%'; 590 table_name column_name data_type is_nullable 591 bool01 col1 INT YES 592 bool01 col2 CHAR YES 593 drop table bool01; 594 drop table if exists char01; 595 create table char01 (col1 int, col2 char); 596 insert into char01 values (1, 'q'); 597 insert into char01 values (2, '*'); 598 insert into char01 values (10, '3'); 599 insert into char01 values (20, '数'); 600 select * from char01; 601 col1 col2 602 1 q 603 2 * 604 10 3 605 20 数 606 alter table char01 modify col2 varchar(20) after col1; 607 show create table char01; 608 Table Create Table 609 char01 CREATE TABLE `char01` (\n`col1` INT DEFAULT NULL,\n`col2` VARCHAR(20) DEFAULT NULL\n) 610 insert into char01 values (100, '**(*(&(*UJHI'); 611 truncate table char01; 612 select * from char01; 613 col1 col2 614 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'char01' and COLUMN_NAME not like '__mo%'; 615 table_name column_name data_type is_nullable 616 char01 col1 INT YES 617 char01 col2 VARCHAR YES 618 drop table char01; 619 drop table if exists char02; 620 create table char02 (col1 int, col2 varchar(10)); 621 insert into char02 values (1, 'w43234rfq'); 622 insert into char02 values (2, 'a32f4'); 623 insert into char02 values (10, '3432t43r4f'); 624 select * from char02; 625 col1 col2 626 1 w43234rfq 627 2 a32f4 628 10 3432t43r4f 629 alter table char02 modify col2 char(20) first; 630 show create table char02; 631 Table Create Table 632 char02 CREATE TABLE `char02` (\n`col2` CHAR(20) DEFAULT NULL,\n`col1` INT DEFAULT NULL\n) 633 insert into char02 values ('738fewhu&^YUH', 100); 634 select * from char02; 635 col2 col1 636 w43234rfq 1 637 a32f4 2 638 3432t43r4f 10 639 738fewhu&^YUH 100 640 update char02 set col1 = 100 where col2 = 'w43234rfq'; 641 select * from char02; 642 col2 col1 643 a32f4 2 644 3432t43r4f 10 645 738fewhu&^YUH 100 646 w43234rfq 100 647 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'char02' and COLUMN_NAME not like '__mo%'; 648 table_name column_name data_type is_nullable 649 char02 col1 INT YES 650 char02 col2 CHAR YES 651 drop table char02; 652 drop table if exists char03; 653 create table char03 (col1 int, col2 char(10)); 654 insert into char03 values (1, 'ahu323ew32'); 655 insert into char03 values (2, '367283r343'); 656 insert into char03 values (3, null); 657 select * from char03; 658 col1 col2 659 1 ahu323ew32 660 2 367283r343 661 3 null 662 alter table char03 modify col2 varchar(5); 663 internal error: Can't cast column from CHAR type to VARCHAR type because of one or more values in that column. Src length 10 is larger than Dest length 5 664 show create table char03; 665 Table Create Table 666 char03 CREATE TABLE `char03` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(10) DEFAULT NULL\n) 667 insert into char03 values (4, 'eyuiwqewq3'); 668 select * from char03; 669 col1 col2 670 1 ahu323ew32 671 2 367283r343 672 3 null 673 4 eyuiwqewq3 674 delete from char03 where col2 = 'eyuiwqewq3'; 675 select * from char03; 676 col1 col2 677 1 ahu323ew32 678 2 367283r343 679 3 null 680 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'char03' and COLUMN_NAME not like '__mo%'; 681 table_name column_name data_type is_nullable 682 char03 col1 INT YES 683 char03 col2 CHAR YES 684 drop table char03; 685 drop table if exists char04; 686 create table char04 (col1 varchar(200), col2 char(10)); 687 insert into char04 values (null, 'ahu323ew32'); 688 insert into char04 values ('23789178942u1uj3ridjfh2d28u49u4ueji32jf2f32ef32894rjk32nv432f432f', '367283r343'); 689 insert into char04 values ('32jhbfchjecmwd%^&^(*&)UJHFRE%^T&YUHIJKNM', null); 690 select * from char04; 691 col1 col2 692 null ahu323ew32 693 23789178942u1uj3ridjfh2d28u49u4ueji32jf2f32ef32894rjk32nv432f432f 367283r343 694 32jhbfchjecmwd%^&^(*&)UJHFRE%^T&YUHIJKNM null 695 alter table char04 modify col1 varchar(100) not null; 696 constraint violation: Column 'col1' cannot be null 697 alter table char04 modify col2 char(10) not null; 698 constraint violation: Column 'col2' cannot be null 699 show create table char04; 700 Table Create Table 701 char04 CREATE TABLE `char04` (\n`col1` VARCHAR(200) DEFAULT NULL,\n`col2` CHAR(10) DEFAULT NULL\n) 702 insert into char04 values (4, 'eyuiwqewq3'); 703 select * from char04; 704 col1 col2 705 null ahu323ew32 706 23789178942u1uj3ridjfh2d28u49u4ueji32jf2f32ef32894rjk32nv432f432f 367283r343 707 32jhbfchjecmwd%^&^(*&)UJHFRE%^T&YUHIJKNM null 708 4 eyuiwqewq3 709 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'char04' and COLUMN_NAME not like '__mo%'; 710 table_name column_name data_type is_nullable 711 char04 col1 VARCHAR YES 712 char04 col2 CHAR YES 713 drop table char04; 714 drop table if exists text01; 715 create table text01 (col1 text primary key , col2 mediumtext unique index, col3 longtext); 716 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 73 near " index, col3 longtext);"; 717 insert into text01 values ('37268434','32718hcuwh432fr234f34g4f34e4','&*&YHRE%^&*YUIHGT^&Y*UIJK'); 718 no such table test.text01 719 insert into text01 values ('jefwyq3uih2r321fr3', '38eu4jfc3w4e3dcewcevewcve', null); 720 no such table test.text01 721 insert into text01 values (null, '2789378u2uifj4234r23', '32r43frecdrfedwq'); 722 no such table test.text01 723 select * from text01; 724 SQL parser error: table "text01" does not exist 725 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'text01' and COLUMN_NAME not like '__mo%'; 726 table_name column_name data_type is_nullable 727 drop table text01; 728 no such table test.text01 729 drop table if exists alter03; 730 create table alter03 (col1 int, col2 binary, col3 decimal); 731 insert into alter03 values (1, 'e', 324214.2134123); 732 insert into alter03 values (2, '4', -242134.3231432); 733 select * from alter03; 734 col1 col2 col3 735 1 e 324214 736 2 4 -242134 737 alter table alter03 modify col1 decimal after col3, modify col2 varbinary(20); 738 show create table alter03; 739 Table Create Table 740 alter03 CREATE TABLE `alter03` (\n`col2` VARBINARY(20) DEFAULT NULL,\n`col3` DECIMAL(38,0) DEFAULT NULL,\n`col1` DECIMAL(38,0) DEFAULT NULL\n) 741 insert into alter03 values ('32143124', 42432321.000, 132432.214234); 742 select * from alter03; 743 col2 col3 col1 744 e 324214 1 745 4 -242134 2 746 32143124 42432321 132432 747 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'alter03' and COLUMN_NAME not like '__mo%'; 748 table_name column_name data_type is_nullable 749 alter03 col1 DECIMAL128 YES 750 alter03 col2 VARBINARY YES 751 alter03 col3 DECIMAL128 YES 752 drop table alter03; 753 drop table if exists alter04; 754 create table alter04 (col1 int not null default 100 primary key ); 755 insert into alter04 values (); 756 insert into alter04 values (101); 757 alter table alter04 modify col1 int auto_increment; 758 show create table alter04; 759 Table Create Table 760 alter04 CREATE TABLE `alter04` (\n`col1` INT NOT NULL AUTO_INCREMENT,\nPRIMARY KEY (`col1`)\n) 761 insert into alter04 values (); 762 insert into alter04 values (); 763 select * from alter04; 764 col1 765 100 766 101 767 102 768 103 769 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'alter04' and COLUMN_NAME not like '__mo%'; 770 table_name column_name data_type is_nullable 771 alter04 col1 INT NO 772 drop table alter04; 773 drop table if exists alter05; 774 create table alter05 (col1 int primary key auto_increment); 775 insert into alter05 values (); 776 insert into alter05 values (); 777 select * from alter05; 778 col1 779 1 780 2 781 alter table alter05 modify col1 int unique key; 782 show create table alter05; 783 Table Create Table 784 alter05 CREATE TABLE `alter05` (\n`col1` INT NOT NULL,\nPRIMARY KEY (`col1`),\nUNIQUE KEY `col1` (`col1`)\n) 785 insert into alter05 values (); 786 invalid input: invalid default value for column 'col1' 787 insert into alter05 values (); 788 invalid input: invalid default value for column 'col1' 789 select * from alter05; 790 col1 791 1 792 2 793 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'alter05' and COLUMN_NAME not like '__mo%'; 794 table_name column_name data_type is_nullable 795 alter05 col1 INT NO 796 drop table alter05; 797 drop table if exists primary01; 798 create table primary01 (col1 int, col2 text); 799 insert into primary01 values (1, 'wq432r43rf32y2493821ijfk2env3ui4y33i24'); 800 insert into primary01 values (2, '243ewfvefreverewfcwr'); 801 alter table primary01 modify col1 float primary key; 802 show create table primary01; 803 Table Create Table 804 primary01 CREATE TABLE `primary01` (\n`col1` FLOAT NOT NULL,\n`col2` TEXT DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 805 insert into primary01 values (1, '432r2f234day89ujfw42342'); 806 Duplicate entry '1' for key 'col1' 807 insert into primary01 values (2378.32423, '234242))())_'); 808 select * from primary01; 809 col1 col2 810 1.0 wq432r43rf32y2493821ijfk2env3ui4y33i24 811 2.0 243ewfvefreverewfcwr 812 2378.3242 234242))())_ 813 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary01' and COLUMN_NAME not like '__mo%'; 814 table_name column_name data_type is_nullable 815 primary01 col1 FLOAT NO 816 primary01 col2 TEXT YES 817 drop table primary01; 818 drop table if exists primary02; 819 create table primary02(col1 int, col2 binary(10)); 820 insert into primary02 values (1, '32143'); 821 insert into primary02 values (1, '3e'); 822 select * from primary02; 823 col1 col2 824 1 32143 825 1 3e 826 alter table primary02 modify col1 int primary key; 827 Duplicate entry '1' for key 'col1' 828 show create table primary02; 829 Table Create Table 830 primary02 CREATE TABLE `primary02` (\n`col1` INT DEFAULT NULL,\n`col2` BINARY(10) DEFAULT NULL\n) 831 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary02' and COLUMN_NAME not like '__mo%'; 832 table_name column_name data_type is_nullable 833 primary02 col1 INT YES 834 primary02 col2 BINARY YES 835 drop table primary02; 836 drop table if exists primary03; 837 create table primary03(col1 int, col2 binary(10)); 838 insert into primary03 values (1, '32143'); 839 insert into primary03 values (2, '3e'); 840 alter table primary03 modify col1 int primary key; 841 show create table primary03; 842 Table Create Table 843 primary03 CREATE TABLE `primary03` (\n`col1` INT NOT NULL,\n`col2` BINARY(10) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 844 insert into primary03 values (3, '*'); 845 insert into primary03 values (3, 'assad'); 846 Duplicate entry '3' for key 'col1' 847 select * from primary03; 848 col1 col2 849 1 32143 850 2 3e 851 3 * 852 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary03' and COLUMN_NAME not like '__mo%'; 853 table_name column_name data_type is_nullable 854 primary03 col1 INT NO 855 primary03 col2 BINARY YES 856 drop table primary03; 857 drop table if exists primary04; 858 create table primary04(col1 int primary key ,col2 varbinary(20)); 859 insert into primary04 values (1, 'qfreqvreq'); 860 insert into primary04 values (2, '324543##'); 861 alter table primary04 modify col1 float; 862 show create table primary04; 863 Table Create Table 864 primary04 CREATE TABLE `primary04` (\n`col1` FLOAT NOT NULL,\n`col2` VARBINARY(20) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 865 insert into primary04 values (1, '324342__'); 866 Duplicate entry '1' for key 'col1' 867 insert into primary04 values (3, 'qw'); 868 select * from primary04; 869 col1 col2 870 1.0 qfreqvreq 871 2.0 324543## 872 3.0 qw 873 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary04' and COLUMN_NAME not like '__mo%'; 874 table_name column_name data_type is_nullable 875 primary04 col1 FLOAT NO 876 primary04 col2 VARBINARY YES 877 drop table primary04; 878 drop table if exists primary05; 879 create table primary05(col1 int primary key ,col2 varbinary(20)); 880 insert into primary05 values (1, 'qfreqvreq'); 881 insert into primary05 values (2, '324543##'); 882 alter table primary05 modify col2 binary(30) primary key; 883 Multiple primary key defined 884 show create table primary05; 885 Table Create Table 886 primary05 CREATE TABLE `primary05` (\n`col1` INT NOT NULL,\n`col2` VARBINARY(20) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 887 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary05' and COLUMN_NAME not like '__mo%'; 888 table_name column_name data_type is_nullable 889 primary05 col1 INT NO 890 primary05 col2 VARBINARY YES 891 drop table primary05; 892 drop table if exists primary06; 893 create table primary06(col1 int primary key ,col2 varbinary(20)); 894 insert into primary06 values (1, 'qfreqvreq'); 895 insert into primary06 values (2, '324543##'); 896 alter table primary06 modify col1 int unsigned primary key; 897 Multiple primary key defined 898 alter table primary06 modify col2 binary(30) primary key; 899 Multiple primary key defined 900 show create table primary06; 901 Table Create Table 902 primary06 CREATE TABLE `primary06` (\n`col1` INT NOT NULL,\n`col2` VARBINARY(20) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 903 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary06' and COLUMN_NAME not like '__mo%'; 904 table_name column_name data_type is_nullable 905 primary06 col1 INT NO 906 primary06 col2 VARBINARY YES 907 drop table primary06; 908 drop table if exists primary07; 909 create table primary07(col1 int ,col2 float, col3 decimal, primary key (col1, col2)); 910 insert into primary07 values (1, 213412.32143, 3214312.34243214242); 911 insert into primary07 values (2, -324.2342432423, -1243.42334234242); 912 alter table primary07 modify col1 double default null; 913 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead 914 alter table primary07 modify col2 int default null; 915 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead 916 show create table primary07; 917 Table Create Table 918 primary07 CREATE TABLE `primary07` (\n`col1` INT NOT NULL,\n`col2` FLOAT NOT NULL,\n`col3` DECIMAL(38,0) DEFAULT NULL,\nPRIMARY KEY (`col1`,`col2`)\n) 919 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'primary07' and COLUMN_NAME not like '__mo%'; 920 table_name column_name data_type is_nullable 921 primary07 col1 INT NO 922 primary07 col2 FLOAT NO 923 primary07 col3 DECIMAL128 YES 924 drop table primary07; 925 drop table if exists primary07; 926 create table primary07(col1 int ,col2 float, col3 decimal, primary key (col1, col2)); 927 insert into primary07 values (1, 213412.32143, 3214312.34243214242); 928 insert into primary07 values (2, -324.2342432423, -1243.42334234242); 929 alter table primary07 modify col1 double not null; 930 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead 931 alter table primary07 modify col2 decimal(28,10); 932 show create table primary07; 933 Table Create Table 934 primary07 CREATE TABLE `primary07` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(28,10) NOT NULL,\n`col3` DECIMAL(38,0) DEFAULT NULL,\nPRIMARY KEY (`col1`,`col2`)\n) 935 drop table primary07; 936 drop table if exists index01; 937 CREATE TABLE index01(a INTEGER not null , b CHAR(10), c date, d decimal(7,2), UNIQUE KEY(a, b)); 938 show create table index01; 939 Table Create Table 940 index01 CREATE TABLE `index01` (\n`a` INT NOT NULL,\n`b` CHAR(10) DEFAULT NULL,\n`c` DATE DEFAULT NULL,\n`d` DECIMAL(7,2) DEFAULT NULL,\nUNIQUE KEY `a` (`a`,`b`)\n) 941 insert into index01 values(1, 'ab', '1980-12-17', 800); 942 insert into index01 values(2, 'ac', '1981-02-20', 1600); 943 insert into index01 values(3, 'ad', '1981-02-22', 500); 944 select * from index01; 945 a b c d 946 1 ab 1980-12-17 800.00 947 2 ac 1981-02-20 1600.00 948 3 ad 1981-02-22 500.00 949 alter table index01 modify b VARCHAR(20); 950 show create table index01; 951 Table Create Table 952 index01 CREATE TABLE `index01` (\n`a` INT NOT NULL,\n`b` VARCHAR(20) DEFAULT NULL,\n`c` DATE DEFAULT NULL,\n`d` DECIMAL(7,2) DEFAULT NULL,\nUNIQUE KEY `a` (`a`,`b`)\n) 953 show index from index01; 954 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 955 index01 0 a 1 a A 0 NULL NULL YES NULL 956 index01 0 a 2 b A 0 NULL NULL YES YES NULL 957 alter table index01 modify b VARCHAR(20) UNIQUE KEY; 958 show index from index01; 959 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 960 index01 0 a 1 a A 0 NULL NULL YES NULL 961 index01 0 a 2 b A 0 NULL NULL YES YES NULL 962 index01 0 b 1 b A 0 NULL NULL YES YES NULL 963 show create table index01; 964 Table Create Table 965 index01 CREATE TABLE `index01` (\n`a` INT NOT NULL,\n`b` VARCHAR(20) DEFAULT NULL,\n`c` DATE DEFAULT NULL,\n`d` DECIMAL(7,2) DEFAULT NULL,\nUNIQUE KEY `a` (`a`,`b`),\nUNIQUE KEY `b` (`b`)\n) 966 select * from index01; 967 a b c d 968 1 ab 1980-12-17 800.00 969 2 ac 1981-02-20 1600.00 970 3 ad 1981-02-22 500.00 971 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'index01' and COLUMN_NAME not like '__mo%'; 972 table_name column_name data_type is_nullable 973 index01 a INT NO 974 index01 b VARCHAR YES 975 index01 c DATE YES 976 index01 d DECIMAL64 YES 977 drop table index01; 978 drop table if exists index02; 979 CREATE TABLE index02(a INTEGER PRIMARY KEY, b CHAR(10), c date, d decimal(7,2), INDEX(a, b), KEY(c)); 980 insert into index02 values(1, 'ab', '1980-12-17', 800); 981 insert into index02 values(2, 'ac', '1981-02-20', 1600); 982 insert into index02 values(3, 'ad', '1981-02-22', 500); 983 select * from index02; 984 a b c d 985 1 ab 1980-12-17 800.00 986 2 ac 1981-02-20 1600.00 987 3 ad 1981-02-22 500.00 988 alter table index02 modify b VARCHAR(20); 989 show index from index02; 990 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 991 index02 1 a 1 a A 0 NULL NULL YES NULL 992 index02 1 a 2 b A 0 NULL NULL YES YES NULL 993 index02 1 c 1 c A 0 NULL NULL YES YES NULL 994 index02 0 PRIMARY 1 a A 0 NULL NULL YES NULL 995 alter table index02 modify b VARCHAR(20) KEY; 996 Multiple primary key defined 997 show create table index02; 998 Table Create Table 999 index02 CREATE TABLE `index02` (\n`a` INT NOT NULL,\n`b` VARCHAR(20) DEFAULT NULL,\n`c` DATE DEFAULT NULL,\n`d` DECIMAL(7,2) DEFAULT NULL,\nPRIMARY KEY (`a`),\nKEY `a` (`a`,`b`),\nKEY `c` (`c`)\n) 1000 alter table index02 modify b VARCHAR(20) UNIQUE KEY; 1001 show index from index02; 1002 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 1003 index02 0 b 1 b A 0 NULL NULL YES YES NULL 1004 index02 1 a 1 a A 0 NULL NULL YES NULL 1005 index02 1 a 2 b A 0 NULL NULL YES YES NULL 1006 index02 1 c 1 c A 0 NULL NULL YES YES NULL 1007 index02 0 PRIMARY 1 a A 0 NULL NULL YES NULL 1008 alter table index02 modify c VARCHAR(20) UNIQUE KEY; 1009 show index from index02; 1010 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 1011 index02 0 b 1 b A 0 NULL NULL YES YES NULL 1012 index02 0 c_2 1 c A 0 NULL NULL YES YES NULL 1013 index02 1 a 1 a A 0 NULL NULL YES NULL 1014 index02 1 a 2 b A 0 NULL NULL YES YES NULL 1015 index02 1 c 1 c A 0 NULL NULL YES YES NULL 1016 index02 0 PRIMARY 1 a A 0 NULL NULL YES NULL 1017 show create table index02; 1018 Table Create Table 1019 index02 CREATE TABLE `index02` (\n`a` INT NOT NULL,\n`b` VARCHAR(20) DEFAULT NULL,\n`c` VARCHAR(20) DEFAULT NULL,\n`d` DECIMAL(7,2) DEFAULT NULL,\nPRIMARY KEY (`a`),\nUNIQUE KEY `b` (`b`),\nUNIQUE KEY `c_2` (`c`),\nKEY `a` (`a`,`b`),\nKEY `c` (`c`)\n) 1020 desc index02; 1021 Field Type Null Key Default Extra Comment 1022 a INT(32) NO PRI null 1023 b VARCHAR(20) YES UNI null 1024 c VARCHAR(20) YES UNI null 1025 d DECIMAL64(7) YES null 1026 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'index02' and COLUMN_NAME not like '__mo%'; 1027 table_name column_name data_type is_nullable 1028 index02 a INT NO 1029 index02 b VARCHAR YES 1030 index02 c VARCHAR YES 1031 index02 d DECIMAL64 YES 1032 select * from index02; 1033 a b c d 1034 1 ab 1980-12-17 800.00 1035 2 ac 1981-02-20 1600.00 1036 3 ad 1981-02-22 500.00 1037 drop table index02; 1038 drop table if exists foreign01; 1039 create table foreign01(col1 int primary key, 1040 col2 varchar(20), 1041 col3 int, 1042 col4 bigint); 1043 drop table if exists foreign02; 1044 create table foreign02(col1 int, 1045 col2 int, 1046 col3 int primary key, 1047 constraint `c1` foreign key(col1) references foreign01(col1)); 1048 show create table foreign01; 1049 Table Create Table 1050 foreign01 CREATE TABLE `foreign01` (\n`col1` INT NOT NULL,\n`col2` VARCHAR(20) DEFAULT NULL,\n`col3` INT DEFAULT NULL,\n`col4` BIGINT DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 1051 show create table foreign02; 1052 Table Create Table 1053 foreign02 CREATE TABLE `foreign02` (\n`col1` INT DEFAULT NULL,\n`col2` INT DEFAULT NULL,\n`col3` INT NOT NULL,\nPRIMARY KEY (`col3`),\nCONSTRAINT `c1` FOREIGN KEY (`col1`) REFERENCES `foreign01` (`col1`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 1054 insert into foreign01 values(1,'sfhuwe',1,1); 1055 insert into foreign01 values(2,'37829901k3d',2,2); 1056 insert into foreign02 values(1,1,1); 1057 insert into foreign02 values(2,2,2); 1058 select * from foreign01; 1059 col1 col2 col3 col4 1060 1 sfhuwe 1 1 1061 2 37829901k3d 2 2 1062 select * from foreign02; 1063 col1 col2 col3 1064 1 1 1 1065 2 2 2 1066 alter table foreign01 modify col1 decimal; 1067 Cannot change column 'col1': used in a foreign key constraint 'c1' of table 'test.foreign02' 1068 alter table foreign02 modify col1 float after col3; 1069 Cannot change column 'col1': used in a foreign key constraint 'c1' 1070 show create table foreign01; 1071 Table Create Table 1072 foreign01 CREATE TABLE `foreign01` (\n`col1` INT NOT NULL,\n`col2` VARCHAR(20) DEFAULT NULL,\n`col3` INT DEFAULT NULL,\n`col4` BIGINT DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 1073 show create table foreign02; 1074 Table Create Table 1075 foreign02 CREATE TABLE `foreign02` (\n`col1` INT DEFAULT NULL,\n`col2` INT DEFAULT NULL,\n`col3` INT NOT NULL,\nPRIMARY KEY (`col3`),\nCONSTRAINT `c1` FOREIGN KEY (`col1`) REFERENCES `foreign01` (`col1`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 1076 alter table foreign01 modify col2 varchar(100); 1077 alter table foreign02 modify col2 double after col3; 1078 show create table foreign01; 1079 Table Create Table 1080 foreign01 CREATE TABLE `foreign01` (\n`col1` INT NOT NULL,\n`col2` VARCHAR(100) DEFAULT NULL,\n`col3` INT DEFAULT NULL,\n`col4` BIGINT DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 1081 show create table foreign02; 1082 Table Create Table 1083 foreign02 CREATE TABLE `foreign02` (\n`col1` INT DEFAULT NULL,\n`col3` INT NOT NULL,\n`col2` DOUBLE DEFAULT NULL,\nPRIMARY KEY (`col3`),\nCONSTRAINT `c1` FOREIGN KEY (`col1`) REFERENCES `foreign01` (`col1`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 1084 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'foreign01' and COLUMN_NAME not like '__mo%'; 1085 table_name column_name data_type is_nullable 1086 foreign01 col1 INT NO 1087 foreign01 col2 VARCHAR YES 1088 foreign01 col3 INT YES 1089 foreign01 col4 BIGINT YES 1090 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'foreign02' and COLUMN_NAME not like '__mo%'; 1091 table_name column_name data_type is_nullable 1092 foreign02 col1 INT YES 1093 foreign02 col2 DOUBLE YES 1094 foreign02 col3 INT NO 1095 drop table foreign02; 1096 drop table foreign01; 1097 drop table if exists decimal01; 1098 create table decimal01 (col1 decimal,col2 decimal(38,10)); 1099 insert into decimal01 values (23746723468723.242334243, 38208439024234.43242342423423423422342); 1100 insert into decimal01 values (32487329847923424, -4324324324.3297932749028949373324242423424); 1101 alter table decimal01 modify column col1 decimal(38,6); 1102 alter table decimal01 modify col2 decimal(38,9); 1103 show create table decimal01; 1104 Table Create Table 1105 decimal01 CREATE TABLE `decimal01` (\n`col1` DECIMAL(38,6) DEFAULT NULL,\n`col2` DECIMAL(38,9) DEFAULT NULL\n) 1106 select * from decimal01; 1107 col1 col2 1108 23746723468723.000000 38208439024234.432423424 1109 32487329847923424.000000 -4324324324.329793275 1110 truncate table decimal01; 1111 alter table decimal01 add column col3 decimal(19,0) first; 1112 show create table decimal01; 1113 Table Create Table 1114 decimal01 CREATE TABLE `decimal01` (\n`col3` DECIMAL(19,0) DEFAULT NULL,\n`col1` DECIMAL(38,6) DEFAULT NULL,\n`col2` DECIMAL(38,9) DEFAULT NULL\n) 1115 insert into decimal01 values (37298342.123, -37829342.3244234, -283794324.2342); 1116 select * from decimal01; 1117 col3 col1 col2 1118 37298342 -37829342.324423 -283794324.234200000 1119 drop table decimal01; 1120 drop table if exists null01; 1121 create table null01(col1 int default null, col2 binary(10)); 1122 insert into null01 values (1, '32143'); 1123 insert into null01 values (null, '3e'); 1124 alter table null01 modify col1 int not null; 1125 constraint violation: Column 'col1' cannot be null 1126 show create table null01; 1127 Table Create Table 1128 null01 CREATE TABLE `null01` (\n`col1` INT DEFAULT null,\n`col2` BINARY(10) DEFAULT NULL\n) 1129 select * from null01; 1130 col1 col2 1131 1 32143 1132 null 3e 1133 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'null01' and COLUMN_NAME not like '__mo%'; 1134 table_name column_name data_type is_nullable 1135 null01 col1 INT YES 1136 null01 col2 BINARY YES 1137 drop table null01; 1138 drop table if exists null02; 1139 create table null02(col1 int default null, col2 varbinary(100)); 1140 insert into null02 values (1, '32143'); 1141 insert into null02 values (2, '3e'); 1142 alter table null02 modify col1 int not null; 1143 show create table null02; 1144 Table Create Table 1145 null02 CREATE TABLE `null02` (\n`col1` INT NOT NULL,\n`col2` VARBINARY(100) DEFAULT NULL\n) 1146 insert into null02 values (null, '1'); 1147 constraint violation: Column 'col1' cannot be null 1148 insert into null02 values (342, 'aesd'); 1149 select * from null02; 1150 col1 col2 1151 1 32143 1152 2 3e 1153 342 aesd 1154 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'null02' and COLUMN_NAME not like '__mo%'; 1155 table_name column_name data_type is_nullable 1156 null02 col1 INT NO 1157 null02 col2 VARBINARY YES 1158 drop table null02; 1159 drop table if exists cast01; 1160 create table cast01 (col1 int, col2 smallint, col3 bigint unsigned, col4 tinyint unsigned); 1161 insert into cast01 values (1, -32768, 12352314214243242, 0); 1162 insert into cast01 values (329884234, 32767, 3828493, 21); 1163 insert into cast01 values (-29302423, 32, 324242132321, 10); 1164 insert into cast01 values (null, null, null, null); 1165 select * from cast01; 1166 col1 col2 col3 col4 1167 1 -32768 12352314214243242 0 1168 329884234 32767 3828493 21 1169 -29302423 32 324242132321 10 1170 null null null null 1171 alter table cast01 modify col1 float; 1172 show create table cast01; 1173 Table Create Table 1174 cast01 CREATE TABLE `cast01` (\n`col1` FLOAT DEFAULT NULL,\n`col2` SMALLINT DEFAULT NULL,\n`col3` BIGINT UNSIGNED DEFAULT NULL,\n`col4` TINYINT UNSIGNED DEFAULT NULL\n) 1175 insert into cast01 values (3271.312432, null, 323254324321432, 100); 1176 select * from cast01; 1177 col1 col2 col3 col4 1178 1.0 -32768 12352314214243242 0 1179 3.2988422E8 32767 3828493 21 1180 -2.9302424E7 32 324242132321 10 1181 null null null null 1182 3271.3125 null 323254324321432 100 1183 alter table cast01 modify col2 double first; 1184 show create table cast01; 1185 Table Create Table 1186 cast01 CREATE TABLE `cast01` (\n`col2` DOUBLE DEFAULT NULL,\n`col1` FLOAT DEFAULT NULL,\n`col3` BIGINT UNSIGNED DEFAULT NULL,\n`col4` TINYINT UNSIGNED DEFAULT NULL\n) 1187 insert into cast01 values (3271834.2134, -3892843.214, 328943232, 255); 1188 select * from cast01; 1189 col2 col1 col3 col4 1190 -32768.0 1.0 12352314214243242 0 1191 32767.0 3.2988422E8 3828493 21 1192 32.0 -2.9302424E7 324242132321 10 1193 null null null null 1194 null 3271.3125 323254324321432 100 1195 3271834.2134 -3892843.2 328943232 255 1196 alter table cast01 modify col3 double; 1197 show create table cast01; 1198 Table Create Table 1199 cast01 CREATE TABLE `cast01` (\n`col2` DOUBLE DEFAULT NULL,\n`col1` FLOAT DEFAULT NULL,\n`col3` DOUBLE DEFAULT NULL,\n`col4` TINYINT UNSIGNED DEFAULT NULL\n) 1200 insert into cast01 values (3271834.2134, -3892843.214, 328943232.3234, 255); 1201 select * from cast01; 1202 col2 col1 col3 col4 1203 -32768.0 1.0 1.2352314214243242E16 0 1204 32767.0 3.2988422E8 3828493.0 21 1205 32.0 -2.9302424E7 3.24242132321E11 10 1206 null null null null 1207 null 3271.3125 3.23254324321432E14 100 1208 3271834.2134 -3892843.2 3.28943232E8 255 1209 3271834.2134 -3892843.2 3.289432323234E8 255 1210 alter table cast01 modify col4 decimal(28,10) after col2; 1211 show create table cast01; 1212 Table Create Table 1213 cast01 CREATE TABLE `cast01` (\n`col2` DOUBLE DEFAULT NULL,\n`col4` DECIMAL(28,10) DEFAULT NULL,\n`col1` FLOAT DEFAULT NULL,\n`col3` DOUBLE DEFAULT NULL\n) 1214 insert into cast01 values (3271834.2134, -3823243.4324, 328943232.3234, -32423.43243); 1215 select * from cast01; 1216 col2 col4 col1 col3 1217 -32768.0 0E-10 1.0 1.2352314214243242E16 1218 32767.0 21.0000000000 3.2988422E8 3828493.0 1219 32.0 10.0000000000 -2.9302424E7 3.24242132321E11 1220 null null null null 1221 null 100.0000000000 3271.3125 3.23254324321432E14 1222 3271834.2134 255.0000000000 -3892843.2 3.28943232E8 1223 3271834.2134 255.0000000000 -3892843.2 3.289432323234E8 1224 3271834.2134 -3823243.4324000000 3.2894323E8 -32423.43243 1225 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cast01' and COLUMN_NAME not like '__mo%'; 1226 table_name column_name data_type is_nullable 1227 cast01 col1 FLOAT YES 1228 cast01 col2 DOUBLE YES 1229 cast01 col3 DOUBLE YES 1230 cast01 col4 DECIMAL128 YES 1231 drop table cast01; 1232 drop table if exists cast02; 1233 create table cast02 (col1 float, col2 double, col3 decimal(30,5), col4 decimal(37,1)); 1234 insert into cast02 values (1.321341, -32768.32142, 1235231421424.3214242134124324323, 12342.43243242121); 1235 insert into cast02 values (329884234.3242, null, 3828493, 21); 1236 insert into cast02 values (93024232.32324, -32.243142, 324242132321, null); 1237 select * from cast02; 1238 col1 col2 col3 col4 1239 1.321341 -32768.32142 1235231421424.32142 12342.4 1240 3.2988422E8 null 3828493.00000 21.0 1241 9.302423E7 -32.243142 324242132321.00000 null 1242 alter table cast02 modify col1 int unsigned; 1243 show create table cast02; 1244 Table Create Table 1245 cast02 CREATE TABLE `cast02` (\n`col1` INT UNSIGNED DEFAULT NULL,\n`col2` DOUBLE DEFAULT NULL,\n`col3` DECIMAL(30,5) DEFAULT NULL,\n`col4` DECIMAL(37,1) DEFAULT NULL\n) 1246 insert into cast02 values (2724.327832, null, 32325432421432, 100.3322142142); 1247 select * from cast02; 1248 col1 col2 col3 col4 1249 1 -32768.32142 1235231421424.32142 12342.4 1250 329884224 null 3828493.00000 21.0 1251 93024232 -32.243142 324242132321.00000 null 1252 2724 null 32325432421432.00000 100.3 1253 alter table cast02 modify col2 bigint; 1254 show create table cast02; 1255 Table Create Table 1256 cast02 CREATE TABLE `cast02` (\n`col1` INT UNSIGNED DEFAULT NULL,\n`col2` BIGINT DEFAULT NULL,\n`col3` DECIMAL(30,5) DEFAULT NULL,\n`col4` DECIMAL(37,1) DEFAULT NULL\n) 1257 insert into cast02 values (1000, 323421423421342, 328943232.321424, -255.321151234); 1258 select * from cast02; 1259 col1 col2 col3 col4 1260 1 -32768 1235231421424.32142 12342.4 1261 329884224 null 3828493.00000 21.0 1262 93024232 -32 324242132321.00000 null 1263 2724 null 32325432421432.00000 100.3 1264 1000 323421423421342 328943232.32142 -255.3 1265 alter table cast02 modify col3 bigint unsigned; 1266 show create table cast02; 1267 Table Create Table 1268 cast02 CREATE TABLE `cast02` (\n`col1` INT UNSIGNED DEFAULT NULL,\n`col2` BIGINT DEFAULT NULL,\n`col3` BIGINT UNSIGNED DEFAULT NULL,\n`col4` DECIMAL(37,1) DEFAULT NULL\n) 1269 insert into cast02 values (32718, 100, 32894323237289, 234); 1270 select * from cast02; 1271 col1 col2 col3 col4 1272 1 -32768 1235231421424 12342.4 1273 329884224 null 3828493 21.0 1274 93024232 -32 324242132321 null 1275 2724 null 32325432421432 100.3 1276 1000 323421423421342 328943232 -255.3 1277 32718 100 32894323237289 234.0 1278 alter table cast02 modify col4 smallint first; 1279 show create table cast02; 1280 Table Create Table 1281 cast02 CREATE TABLE `cast02` (\n`col4` SMALLINT DEFAULT NULL,\n`col1` INT UNSIGNED DEFAULT NULL,\n`col2` BIGINT DEFAULT NULL,\n`col3` BIGINT UNSIGNED DEFAULT NULL\n) 1282 insert into cast02 values (234, 32718, 100, 32894323237289); 1283 select * from cast02; 1284 col4 col1 col2 col3 1285 12342 1 -32768 1235231421424 1286 21 329884224 null 3828493 1287 null 93024232 -32 324242132321 1288 100 2724 null 32325432421432 1289 -255 1000 323421423421342 328943232 1290 234 32718 100 32894323237289 1291 234 32718 100 32894323237289 1292 drop table cast02; 1293 drop table if exists cast03; 1294 create table cast03 (col1 smallint unsigned, col2 float, col3 double, col4 decimal); 1295 insert into cast03 values (1, 323242.34242, 23432.3242, 8329498352.32534242323432); 1296 insert into cast03 values (200, -213443.321412, 32424.342424242, 0.382943424324234); 1297 insert into cast03 (col1, col2, col3, col4) values (null, null, null, null); 1298 alter table cast03 modify col1 char(50), modify col2 char(100), modify col3 varchar(50), modify col4 varchar(15) first; 1299 show create table cast03; 1300 Table Create Table 1301 cast03 CREATE TABLE `cast03` (\n`col4` VARCHAR(15) DEFAULT NULL,\n`col1` CHAR(50) DEFAULT NULL,\n`col2` CHAR(100) DEFAULT NULL,\n`col3` VARCHAR(50) DEFAULT NULL\n) 1302 insert into cast03 values ('3243342', '3242f()', '4728947234342,', '457328990r3if943i4u9owiuo4ewfr3w4r3fre'); 1303 select * from cast03; 1304 col4 col1 col2 col3 1305 8329498352 1 323242.34 23432.3242 1306 0 200 -213443.33 32424.342424242 1307 null null null null 1308 3243342 3242f() 4728947234342, 457328990r3if943i4u9owiuo4ewfr3w4r3fre 1309 drop table cast03; 1310 drop table if exists cast04; 1311 create table cast04 (col1 char, col2 varchar, col3 text, col4 blob); 1312 insert into cast04 values ('1', '-281321.21312', '328', '327482739.32413'); 1313 insert into cast04 values ('0', '3412234321', '-332134324.2432423423423', '-1032412.4324'); 1314 insert into cast04 values (null, null, null, null); 1315 alter table cast04 modify col1 int unsigned; 1316 alter table cast04 modify col2 decimal(34,4) after col4; 1317 show create table cast04; 1318 Table Create Table 1319 cast04 CREATE TABLE `cast04` (\n`col1` INT UNSIGNED DEFAULT NULL,\n`col3` TEXT DEFAULT NULL,\n`col4` BLOB DEFAULT NULL,\n`col2` DECIMAL(34,4) DEFAULT NULL\n) 1320 alter table cast04 modify col3 double, modify col4 float; 1321 show create table cast04; 1322 Table Create Table 1323 cast04 CREATE TABLE `cast04` (\n`col1` INT UNSIGNED DEFAULT NULL,\n`col3` DOUBLE DEFAULT NULL,\n`col4` FLOAT DEFAULT NULL,\n`col2` DECIMAL(34,4) DEFAULT NULL\n) 1324 insert into cast04 values (); 1325 select * from cast04; 1326 col1 col3 col4 col2 1327 1 328.0 3.2748275E8 -281321.2131 1328 0 -3.321343242432423E8 -1032412.44 3412234321.0000 1329 null null null null 1330 null null null null 1331 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cast04' and COLUMN_NAME not like '__mo%'; 1332 table_name column_name data_type is_nullable 1333 cast04 col1 INT UNSIGNED YES 1334 cast04 col2 DECIMAL128 YES 1335 cast04 col3 DOUBLE YES 1336 cast04 col4 FLOAT YES 1337 drop table cast04; 1338 drop table if exists cast05; 1339 create table cast05 (col1 date); 1340 insert into cast05 values ('1997-01-13'); 1341 insert into cast05 values ('2023-12-12'); 1342 insert into cast05 values (null); 1343 select * from cast05; 1344 col1 1345 1997-01-13 1346 2023-12-12 1347 null 1348 alter table cast05 modify col1 varchar(100); 1349 alter table cast05 modify col1 datetime first; 1350 select * from cast05; 1351 col1 1352 1997-01-13 00:00:00 1353 2023-12-12 00:00:00 1354 null 1355 show create table cast05; 1356 Table Create Table 1357 cast05 CREATE TABLE `cast05` (\n`col1` DATETIME DEFAULT NULL\n) 1358 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cast05' and COLUMN_NAME not like '__mo%'; 1359 table_name column_name data_type is_nullable 1360 cast05 col1 DATETIME YES 1361 drop table cast05; 1362 drop table if exists abnormal02; 1363 create table abnormal02 (col1 int unsigned not null, col2 smallint unsigned); 1364 insert into abnormal02 values (2147483647, 255); 1365 insert into abnormal02 values (3242334, 10); 1366 select * from abnormal02; 1367 col1 col2 1368 2147483647 255 1369 3242334 10 1370 alter table abnormal02 modify col1 int; 1371 alter table abnormal02 modify col2 smallint; 1372 select * from abnormal02; 1373 col1 col2 1374 2147483647 255 1375 3242334 10 1376 show create table abnormal02; 1377 Table Create Table 1378 abnormal02 CREATE TABLE `abnormal02` (\n`col1` INT DEFAULT NULL,\n`col2` SMALLINT DEFAULT NULL\n) 1379 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal02' and COLUMN_NAME not like '__mo%'; 1380 table_name column_name data_type is_nullable 1381 abnormal02 col1 INT YES 1382 abnormal02 col2 SMALLINT YES 1383 drop table abnormal02; 1384 drop table if exists abnormal01; 1385 create table abnormal01 (col1 int, col2 bigint, col3 decimal); 1386 insert into abnormal01 values (2147483647, 9223372036854775807, 3291843920.32783); 1387 insert into abnormal01 values (-21843243, 832942343241999999, -2787343243.2343); 1388 insert into abnormal01 values (null, null, null); 1389 select * from abnormal01; 1390 col1 col2 col3 1391 2147483647 9223372036854775807 3291843920 1392 -21843243 832942343241999999 -2787343243 1393 null null null 1394 alter table abnormal01 modify col2 int; 1395 Data truncation: data out of range: data type int32, value '9223372036854775807' 1396 alter table abnormal01 modify col1 smallint; 1397 Data truncation: data out of range: data type int16, value '2147483647' 1398 alter table abnormal01 modify col3 decimal(10,0); 1399 select * from abnormal01; 1400 col1 col2 col3 1401 2147483647 9223372036854775807 3291843920 1402 -21843243 832942343241999999 -2787343243 1403 null null null 1404 show create table abnormal01; 1405 Table Create Table 1406 abnormal01 CREATE TABLE `abnormal01` (\n`col1` INT DEFAULT NULL,\n`col2` BIGINT DEFAULT NULL,\n`col3` DECIMAL(10,0) DEFAULT NULL\n) 1407 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal01' and COLUMN_NAME not like '__mo%'; 1408 table_name column_name data_type is_nullable 1409 abnormal01 col1 INT YES 1410 abnormal01 col2 BIGINT YES 1411 abnormal01 col3 DECIMAL64 YES 1412 drop table abnormal01; 1413 drop table if exists abnormal02; 1414 create table abnormal02 (col1 int not null, col2 smallint); 1415 insert into abnormal02 values (-2147483647, 255); 1416 insert into abnormal02 values (3242334, -10); 1417 select * from abnormal02; 1418 col1 col2 1419 -2147483647 255 1420 3242334 -10 1421 alter table abnormal02 modify col1 int unsigned; 1422 Data truncation: data out of range: data type uint, value '-2147483647' 1423 alter table abnormal02 modify col2 smallint unsigned; 1424 Data truncation: data out of range: data type uint, value '-10' 1425 select * from abnormal02; 1426 col1 col2 1427 -2147483647 255 1428 3242334 -10 1429 show create table abnormal02; 1430 Table Create Table 1431 abnormal02 CREATE TABLE `abnormal02` (\n`col1` INT NOT NULL,\n`col2` SMALLINT DEFAULT NULL\n) 1432 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal02' and COLUMN_NAME not like '__mo%'; 1433 table_name column_name data_type is_nullable 1434 abnormal02 col1 INT NO 1435 abnormal02 col2 SMALLINT YES 1436 drop table abnormal02; 1437 drop table if exists abnormal03; 1438 create table abnormal03 (col1 datetime, col2 blob); 1439 insert into abnormal03 values ('1997-01-13 00:00:00', '342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f'); 1440 insert into abnormal03 values ('2012-01-13 23:23:59', '63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573'); 1441 select * from abnormal03; 1442 col1 col2 1443 1997-01-13 00:00:00 342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f 1444 2012-01-13 23:23:59 63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573 1445 alter table abnormal03 modify col1 varchar(100); 1446 alter table abnormal03 modify col2 int; 1447 invalid argument cast to int, bad value 342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f 1448 select * from abnormal03; 1449 col1 col2 1450 1997-01-13 00:00:00 342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f 1451 2012-01-13 23:23:59 63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573 1452 show create table abnormal03; 1453 Table Create Table 1454 abnormal03 CREATE TABLE `abnormal03` (\n`col1` VARCHAR(100) DEFAULT NULL,\n`col2` BLOB DEFAULT NULL\n) 1455 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal03' and COLUMN_NAME not like '__mo%'; 1456 table_name column_name data_type is_nullable 1457 abnormal03 col1 VARCHAR YES 1458 abnormal03 col2 BLOB YES 1459 drop table abnormal03; 1460 drop table if exists abnormal04; 1461 create table abnormal04 (col1 json, col2 enum('a', 'b')); 1462 insert into abnormal04 values (('{"x": 17, "x": "red"}'),'a'); 1463 insert into abnormal04 values (('{"x": 17, "x": "red", "x": [3, 5, 7]}'), 'b'); 1464 select * from abnormal04; 1465 col1 col2 1466 {"x": "red"} a 1467 {"x": [3, 5, 7]} b 1468 alter table abnormal04 modify col1 float; 1469 invalid argument operator cast, bad value [JSON FLOAT] 1470 alter table abnormal04 modify col2 int; 1471 invalid argument cast to int, bad value a 1472 select * from abnormal04; 1473 col1 col2 1474 {"x": "red"} a 1475 {"x": [3, 5, 7]} b 1476 show create table abnormal04; 1477 Table Create Table 1478 abnormal04 CREATE TABLE `abnormal04` (\n`col1` JSON DEFAULT NULL,\n`col2` ENUM('a','b') DEFAULT NULL\n) 1479 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal04' and COLUMN_NAME not like '__mo%'; 1480 table_name column_name data_type is_nullable 1481 abnormal04 col1 JSON YES 1482 abnormal04 col2 ENUM YES 1483 drop table abnormal04; 1484 drop table if exists abnormal05; 1485 create temporary table abnormal05 (col1 datetime, col2 blob); 1486 insert into abnormal05 values ('1997-01-13 00:00:00', '342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f'); 1487 insert into abnormal05 values ('2012-01-13 23:23:59', '63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573'); 1488 select * from abnormal05; 1489 col1 col2 1490 1997-01-13 00:00:00 342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f 1491 2012-01-13 23:23:59 63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573 1492 alter table abnormal05 modify col1 float; 1493 alter table for temporary table is not yet implemented 1494 alter table abnormal05 modify col2 int; 1495 alter table for temporary table is not yet implemented 1496 select * from abnormal05; 1497 col1 col2 1498 1997-01-13 00:00:00 342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f 1499 2012-01-13 23:23:59 63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573 1500 show create table abnormal05; 1501 Table Create Table 1502 abnormal05 CREATE TABLE `abnormal05` (\n`col1` DATETIME DEFAULT NULL,\n`col2` BLOB DEFAULT NULL\n) 1503 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'abnormal05' and COLUMN_NAME not like '__mo%'; 1504 table_name column_name data_type is_nullable 1505 drop table abnormal05; 1506 drop table if exists view01; 1507 drop table if exists view02; 1508 create table view01 (a int); 1509 insert into view01 values (1),(2); 1510 create table view02 (a int); 1511 insert into view02 values (1); 1512 create view v0 as select view01.a, view02.a as b from view01 left join view02 using(a); 1513 show create view v0; 1514 View Create View character_set_client collation_connection 1515 v0 create view v0 as select view01.a, view02.a as b from view01 left join view02 using(a); utf8mb4 utf8mb4_general_ci 1516 alter table view01 modify a float; 1517 show create table view01; 1518 Table Create Table 1519 view01 CREATE TABLE `view01` (\n`a` FLOAT DEFAULT NULL\n) 1520 show create view v0; 1521 View Create View character_set_client collation_connection 1522 v0 create view v0 as select view01.a, view02.a as b from view01 left join view02 using(a); utf8mb4 utf8mb4_general_ci 1523 drop table view01; 1524 drop table view02; 1525 drop view v0; 1526 drop table if exists cluster01; 1527 create table cluster01(col1 int, col2 decimal) cluster by col1; 1528 insert into cluster01 values (1, 389234924); 1529 insert into cluster01 values (2, -2893428); 1530 alter table cluster01 modify column col2 float; 1531 alter table cluster01 modify col1 double after col2; 1532 show create table cluster01; 1533 Table Create Table 1534 cluster01 CREATE TABLE `cluster01` (\n`col2` FLOAT DEFAULT NULL,\n`col1` DOUBLE DEFAULT NULL\n) CLUSTER BY (`col1`) 1535 select * from cluster01; 1536 col2 col1 1537 3.892349E8 1.0 1538 -2893428.0 2.0 1539 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'cluster01' and COLUMN_NAME not like '__mo%'; 1540 table_name column_name data_type is_nullable 1541 cluster01 col1 DOUBLE YES 1542 cluster01 col2 FLOAT YES 1543 drop table cluster01; 1544 drop table if exists insert01; 1545 create table insert01 (col1 int, col2 decimal(20,10), col3 date); 1546 alter table insert01 modify col1 float; 1547 insert into insert01 values (1, 2378324.324324234, '2020-12-12'); 1548 insert into insert01 values (2, -372893894.32234230000, '1970-01-01'); 1549 alter table insert01 modify col1 float; 1550 alter table insert01 modify col2 decimal(30,1); 1551 show create table insert01; 1552 Table Create Table 1553 insert01 CREATE TABLE `insert01` (\n`col1` FLOAT DEFAULT NULL,\n`col2` DECIMAL(30,1) DEFAULT NULL,\n`col3` DATE DEFAULT NULL\n) 1554 insert into insert01 select * from insert01; 1555 select * from insert01; 1556 col1 col2 col3 1557 1.0 2378324.3 2020-12-12 1558 2.0 -372893894.3 1970-01-01 1559 1.0 2378324.3 2020-12-12 1560 2.0 -372893894.3 1970-01-01 1561 drop table insert01; 1562 drop table if exists time01; 1563 create table time01 (col1 date, col2 datetime, col3 timestamp, col4 time); 1564 insert into time01 values ('2020-01-01', '2000-10-10 12:12:12', '1970-01-01 12:23:59.323000', '01:01:29'); 1565 insert into time01 values ('1997-01-13', null, '1989-01-01 23:23:59.100000', '23:23:59'); 1566 insert into time01 (col1, col2, col3, col4) values ('2030-12-31', '2031-09-09 01:01:01', '2013-12-12 10:10:10.125000', '10:12:12'); 1567 select * from time01; 1568 col1 col2 col3 col4 1569 2020-01-01 2000-10-10 12:12:12 1970-01-01 12:23:59 01:01:29 1570 1997-01-13 null 1989-01-01 23:23:59 23:23:59 1571 2030-12-31 2031-09-09 01:01:01 2013-12-12 10:10:10 10:12:12 1572 alter table time01 modify col1 int, modify col2 int first, modify col3 int after col1, modify col4 int; 1573 show create table time01; 1574 Table Create Table 1575 time01 CREATE TABLE `time01` (\n`col2` INT DEFAULT NULL,\n`col1` INT DEFAULT NULL,\n`col3` INT DEFAULT NULL,\n`col4` INT DEFAULT NULL\n) 1576 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'time01' and COLUMN_NAME not like '__mo%'; 1577 table_name column_name data_type is_nullable 1578 time01 col1 INT YES 1579 time01 col2 INT YES 1580 time01 col3 INT YES 1581 time01 col4 INT YES 1582 drop table time01; 1583 drop table if exists time02; 1584 create table time02 (col2 datetime, col3 timestamp, col4 time); 1585 insert into time02 values ('2000-10-10 12:12:12', '1970-01-01 12:23:59.323000', '01:01:29'); 1586 insert into time02 values ( null, '1889-01-01 23:23:59.125000', '23:23:59'); 1587 insert into time02 (col2, col3, col4) values ('2031-09-09 01:01:01', '2013-12-12 10:10:10.125000', '10:12:12'); 1588 select * from time02; 1589 col2 col3 col4 1590 2000-10-10 12:12:12 1970-01-01 12:23:59 01:01:29 1591 null 1889-01-01 23:23:59 23:23:59 1592 2031-09-09 01:01:01 2013-12-12 10:10:10 10:12:12 1593 alter table time02 modify col2 decimal(20,10) first, modify col3 decimal after col2, modify col4 decimal(38,0); 1594 show create table time02; 1595 Table Create Table 1596 time02 CREATE TABLE `time02` (\n`col2` DECIMAL(20,10) DEFAULT NULL,\n`col3` DECIMAL(38,0) DEFAULT NULL,\n`col4` DECIMAL(38,0) DEFAULT NULL\n) 1597 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'time02' and COLUMN_NAME not like '__mo%'; 1598 table_name column_name data_type is_nullable 1599 time02 col2 DECIMAL128 YES 1600 time02 col3 DECIMAL128 YES 1601 time02 col4 DECIMAL128 YES 1602 drop table time02; 1603 drop table if exists prepare01; 1604 create table prepare01(col1 int, col2 char); 1605 insert into prepare01 values (1,'a'),(2,'b'),(3,'c'); 1606 prepare s1 from 'alter table prepare01 modify col1 decimal primary key'; 1607 execute s1; 1608 show create table prepare01; 1609 Table Create Table 1610 prepare01 CREATE TABLE `prepare01` (\n`col1` DECIMAL(38,0) NOT NULL,\n`col2` CHAR(1) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 1611 prepare s2 from 'alter table prepare01 modify col2 varchar(100) not null'; 1612 execute s2; 1613 show create table prepare01; 1614 Table Create Table 1615 prepare01 CREATE TABLE `prepare01` (\n`col1` DECIMAL(38,0) NOT NULL,\n`col2` VARCHAR(100) NOT NULL,\nPRIMARY KEY (`col1`)\n) 1616 drop table prepare01; 1617 drop role if exists role_r1; 1618 drop user if exists role_u1; 1619 drop table if exists test01; 1620 create role role_r1; 1621 create user role_u1 identified by '111' default role role_r1; 1622 create table test01(col1 int); 1623 insert into test01 values(1); 1624 insert into test01 values(2); 1625 grant create database on account * to role_r1; 1626 grant show databases on account * to role_r1; 1627 grant connect on account * to role_r1; 1628 grant select on table * to role_r1; 1629 grant show tables on database * to role_r1; 1630 use alter_table_modify_column; 1631 alter table test01 modify col1 int primary key; 1632 internal error: do not have privilege to execute the statement 1633 grant alter table on database * to role_r1; 1634 use alter_table_modify_column; 1635 alter table test01 modify col1 int primary key; 1636 no such table alter_table_modify_column.test01 1637 show create table test01; 1638 no such table alter_table_modify_column.test01 1639 show create table test01; 1640 Table Create Table 1641 test01 CREATE TABLE `test01` (\n`col1` INT DEFAULT NULL\n) 1642 drop table test01; 1643 drop role role_r1; 1644 drop user role_u1; 1645 drop table if exists mix01; 1646 create table mix01 (col1 int not null , col2 decimal, col3 date, col4 varchar(100)); 1647 insert into mix01 values (1, 23849234.324, '2100-01-01', 'qy83uhfbh234y78y&*%^&%$$$E%^&Y*UIJNHBGVFTY^&Y*UJHBGVTF^&*U(OK'); 1648 insert into mix01 values (2, 773892.32748000000000, '1997-01-13', '38782yhbf3uhy4iendb32gefdc7y834uh2neujdr2h4f3v43'); 1649 insert into mix01 values (3, -82913942.3434, null, null); 1650 select * from mix01; 1651 col1 col2 col3 col4 1652 1 23849234 2100-01-01 qy83uhfbh234y78y&*%^&%$$$E%^&Y*UIJNHBGVFTY^&Y*UJHBGVTF^&*U(OK 1653 2 773892 1997-01-13 38782yhbf3uhy4iendb32gefdc7y834uh2neujdr2h4f3v43 1654 3 -82913942 null null 1655 alter table mix01 add column col1_2 binary after col1; 1656 show create table mix01; 1657 Table Create Table 1658 mix01 CREATE TABLE `mix01` (\n`col1` INT NOT NULL,\n`col1_2` BINARY(1) DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\n`col3` DATE DEFAULT NULL,\n`col4` VARCHAR(100) DEFAULT NULL\n) 1659 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'mix01' and COLUMN_NAME not like '__mo%'; 1660 table_name column_name data_type is_nullable 1661 mix01 col1 INT NO 1662 mix01 col1_2 BINARY YES 1663 mix01 col2 DECIMAL128 YES 1664 mix01 col3 DATE YES 1665 mix01 col4 VARCHAR YES 1666 alter table mix01 modify column col1_2 varbinary(10) first; 1667 show create table mix01; 1668 Table Create Table 1669 mix01 CREATE TABLE `mix01` (\n`col1_2` VARBINARY(10) DEFAULT NULL,\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\n`col3` DATE DEFAULT NULL,\n`col4` VARCHAR(100) DEFAULT NULL\n) 1670 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'mix01' and COLUMN_NAME not like '__mo%'; 1671 table_name column_name data_type is_nullable 1672 mix01 col1 INT NO 1673 mix01 col1_2 VARBINARY YES 1674 mix01 col2 DECIMAL128 YES 1675 mix01 col3 DATE YES 1676 mix01 col4 VARCHAR YES 1677 truncate table mix01; 1678 alter table mix01 add column col5 int; 1679 show create table mix01; 1680 Table Create Table 1681 mix01 CREATE TABLE `mix01` (\n`col1_2` VARBINARY(10) DEFAULT NULL,\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\n`col3` DATE DEFAULT NULL,\n`col4` VARCHAR(100) DEFAULT NULL,\n`col5` INT DEFAULT NULL\n) 1682 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'mix01' and COLUMN_NAME not like '__mo%'; 1683 table_name column_name data_type is_nullable 1684 mix01 col1 INT NO 1685 mix01 col1_2 VARBINARY YES 1686 mix01 col2 DECIMAL128 YES 1687 mix01 col3 DATE YES 1688 mix01 col4 VARCHAR YES 1689 mix01 col5 INT YES 1690 drop table mix01; 1691 drop table if exists table01; 1692 begin; 1693 create table table01(col1 int, col2 decimal); 1694 insert into table01 values(100,200); 1695 alter table table01 modify column col1 float; 1696 commit; 1697 select * from table01; 1698 col1 col2 1699 100.0 200 1700 select col1 from table01; 1701 col1 1702 100.0 1703 drop table table01; 1704 drop table if exists t1; 1705 create table t1 (a int,b enum('abc','def','database')); 1706 insert into t1 values (1, 'abc'); 1707 insert into t1 values (2, 'database'); 1708 select * from t1; 1709 a b 1710 1 abc 1711 2 database 1712 alter table t1 modify column b enum('abc','def','database') first; 1713 show create table t1; 1714 Table Create Table 1715 t1 CREATE TABLE `t1` (\n`b` ENUM('abc','def','database') DEFAULT NULL,\n`a` INT DEFAULT NULL\n) 1716 select * from t1; 1717 b a 1718 abc 1 1719 database 2 1720 drop table t1; 1721 drop database test;