1drop table if exists t1,t2,t3; 2SET SQL_WARNINGS=1; 3CREATE TABLE t1 ( 4auto int(5) unsigned NOT NULL auto_increment, 5string char(10) default "hello", 6tiny tinyint(4) DEFAULT '0' NOT NULL , 7short smallint(6) DEFAULT '1' NOT NULL , 8medium mediumint(8) DEFAULT '0' NOT NULL, 9long_int int(11) DEFAULT '0' NOT NULL, 10longlong bigint(13) DEFAULT '0' NOT NULL, 11real_float float(13,1) DEFAULT 0.0 NOT NULL, 12real_double double(16,4), 13utiny tinyint(3) unsigned DEFAULT '0' NOT NULL, 14ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL, 15umedium mediumint(8) unsigned DEFAULT '0' NOT NULL, 16ulong int(11) unsigned DEFAULT '0' NOT NULL, 17ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL, 18time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 19date_field date, 20time_field time, 21date_time datetime, 22blob_col blob, 23tinyblob_col tinyblob, 24mediumblob_col mediumblob not null default '', 25longblob_col longblob not null default '', 26options enum('one','two','tree') not null , 27flags set('one','two','tree') not null default '', 28PRIMARY KEY (auto), 29KEY (utiny), 30KEY (tiny), 31KEY (short), 32KEY any_name (medium), 33KEY (longlong), 34KEY (real_float), 35KEY (ushort), 36KEY (umedium), 37KEY (ulong), 38KEY (ulonglong,ulong), 39KEY (options,flags) 40); 41show full fields from t1; 42Field Type Collation Null Key Default Extra Privileges Comment 43auto int(5) unsigned NULL NO PRI NULL auto_increment # 44string char(10) latin1_swedish_ci YES hello # 45tiny tinyint(4) NULL NO MUL 0 # 46short smallint(6) NULL NO MUL 1 # 47medium mediumint(8) NULL NO MUL 0 # 48long_int int(11) NULL NO 0 # 49longlong bigint(13) NULL NO MUL 0 # 50real_float float(13,1) NULL NO MUL 0.0 # 51real_double double(16,4) NULL YES NULL # 52utiny tinyint(3) unsigned NULL NO MUL 0 # 53ushort smallint(5) unsigned zerofill NULL NO MUL 00000 # 54umedium mediumint(8) unsigned NULL NO MUL 0 # 55ulong int(11) unsigned NULL NO MUL 0 # 56ulonglong bigint(13) unsigned NULL NO MUL 0 # 57time_stamp timestamp NULL NO current_timestamp() on update current_timestamp() # 58date_field date NULL YES NULL # 59time_field time NULL YES NULL # 60date_time datetime NULL YES NULL # 61blob_col blob NULL YES NULL # 62tinyblob_col tinyblob NULL YES NULL # 63mediumblob_col mediumblob NULL NO '' # 64longblob_col longblob NULL NO '' # 65options enum('one','two','tree') latin1_swedish_ci NO MUL NULL # 66flags set('one','two','tree') latin1_swedish_ci NO # 67show keys from t1; 68Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 69t1 0 PRIMARY 1 auto A 0 NULL NULL BTREE 70t1 1 utiny 1 utiny A NULL NULL NULL BTREE 71t1 1 tiny 1 tiny A NULL NULL NULL BTREE 72t1 1 short 1 short A NULL NULL NULL BTREE 73t1 1 any_name 1 medium A NULL NULL NULL BTREE 74t1 1 longlong 1 longlong A NULL NULL NULL BTREE 75t1 1 real_float 1 real_float A NULL NULL NULL BTREE 76t1 1 ushort 1 ushort A NULL NULL NULL BTREE 77t1 1 umedium 1 umedium A NULL NULL NULL BTREE 78t1 1 ulong 1 ulong A NULL NULL NULL BTREE 79t1 1 ulonglong 1 ulonglong A NULL NULL NULL BTREE 80t1 1 ulonglong 2 ulong A NULL NULL NULL BTREE 81t1 1 options 1 options A NULL NULL NULL BTREE 82t1 1 options 2 flags A NULL NULL NULL BTREE 83CREATE UNIQUE INDEX test on t1 ( auto ) ; 84CREATE INDEX test2 on t1 ( ulonglong,ulong) ; 85Warnings: 86Note 1831 Duplicate index `test2`. This is deprecated and will be disallowed in a future release 87CREATE INDEX test3 on t1 ( medium ) ; 88Warnings: 89Note 1831 Duplicate index `test3`. This is deprecated and will be disallowed in a future release 90DROP INDEX test ON t1; 91insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,0,0,0,1,1,1,1,'one','one'); 92insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,NULL,2,2,'two','two,one'); 93insert ignore into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3); 94Warnings: 95Warning 1265 Data truncated for column 'string' at row 1 96insert ignore into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1); 97Warnings: 98Warning 1264 Out of range value for column 'utiny' at row 1 99Warning 1264 Out of range value for column 'ushort' at row 1 100Warning 1264 Out of range value for column 'umedium' at row 1 101Warning 1264 Out of range value for column 'ulong' at row 1 102Warning 1264 Out of range value for column 'ulonglong' at row 1 103Warning 1265 Data truncated for column 'options' at row 1 104Warning 1265 Data truncated for column 'flags' at row 1 105insert ignore into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,"one,two,tree"); 106Warnings: 107Warning 1265 Data truncated for column 'string' at row 1 108Warning 1264 Out of range value for column 'tiny' at row 1 109Warning 1264 Out of range value for column 'short' at row 1 110Warning 1264 Out of range value for column 'medium' at row 1 111Warning 1264 Out of range value for column 'long_int' at row 1 112Warning 1264 Out of range value for column 'utiny' at row 1 113Warning 1264 Out of range value for column 'ushort' at row 1 114Warning 1264 Out of range value for column 'umedium' at row 1 115Warning 1264 Out of range value for column 'ulong' at row 1 116Warning 1264 Out of range value for column 'ulonglong' at row 1 117Warning 1265 Data truncated for column 'options' at row 1 118insert ignore into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,0,4294967295,4294967295,4294967295,'4294967295',0,0); 119Warnings: 120Warning 1264 Out of range value for column 'tiny' at row 1 121Warning 1264 Out of range value for column 'short' at row 1 122Warning 1264 Out of range value for column 'medium' at row 1 123Warning 1264 Out of range value for column 'long_int' at row 1 124Warning 1264 Out of range value for column 'utiny' at row 1 125Warning 1264 Out of range value for column 'ushort' at row 1 126Warning 1264 Out of range value for column 'umedium' at row 1 127Warning 1265 Data truncated for column 'options' at row 1 128insert into t1 (tiny) values (1); 129select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000),date_field,time_field,date_time,blob_col,tinyblob_col,mediumblob_col,longblob_col from t1; 130auto string tiny short medium long_int longlong real_float real_double utiny ushort umedium ulong ulonglong mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000) date_field time_field date_time blob_col tinyblob_col mediumblob_col longblob_col 13110 1 1 1 1 1 1 1.0 1.0000 1 00001 1 1 1 0 0000-00-00 00:00:00 0000-00-00 00:00:00 1 1 1 1 13211 2 2 2 2 2 2 2.0 2.0000 2 00002 2 2 2 0 NULL NULL NULL NULL NULL 2 2 13312 0.33333333 3 3 3 3 3 3.0 3.0000 3 00003 3 3 3 0 1997-03-03 10:10:10 1997-03-03 10:10:10 3 13413 -1 -1 -1 -1 -1 -1 -1.0 -1.0000 0 00000 0 0 0 0 1997-08-07 08:07:06 1997-04-03 09:08:07 -1 -1 -1 -1 13514 -429496729 -128 -32768 -8388608 -2147483648 -4294967295 -4294967296.0 -4294967295.0000 0 00000 0 0 0 0 0000-00-00 00:00:00 0000-00-00 00:00:00 -4294967295 -4294967295 -4294967295 -4294967295 13615 4294967295 127 32767 8388607 2147483647 4294967295 4294967296.0 4294967295.0000 255 65535 16777215 4294967295 4294967295 0 0000-00-00 00:00:00 0000-00-00 00:00:00 4294967295 4294967295 4294967295 4294967295 13716 hello 1 1 0 0 0 0.0 NULL 0 00000 0 0 0 0 NULL NULL NULL NULL NULL 138ALTER TABLE t1 139add new_field char(10) default "new" not null, 140change blob_col new_blob_col varchar(20), 141change date_field date_field char(10), 142alter column string set default "newdefault", 143alter short drop default, 144DROP INDEX utiny, 145DROP INDEX ushort, 146DROP PRIMARY KEY, 147DROP FOREIGN KEY any_name, 148ADD INDEX (auto); 149LOCK TABLES t1 WRITE; 150ALTER TABLE t1 151RENAME as t2, 152DROP longblob_col; 153UNLOCK TABLES; 154ALTER TABLE t2 rename as t3; 155LOCK TABLES t3 WRITE ; 156ALTER TABLE t3 rename as t1; 157UNLOCK TABLES; 158select auto,new_field,new_blob_col,date_field from t1 ; 159auto new_field new_blob_col date_field 16010 new 1 0000-00-00 16111 new NULL NULL 16212 new 1997-03-03 16313 new -1 1997-08-07 16414 new -4294967295 0000-00-00 16515 new 4294967295 0000-00-00 16616 new NULL NULL 167CREATE TABLE t2 ( 168auto int(5) unsigned NOT NULL auto_increment, 169string char(20), 170mediumblob_col mediumblob not null, 171new_field char(2), 172PRIMARY KEY (auto) 173); 174INSERT IGNORE INTO t2 (string,mediumblob_col,new_field) SELECT string,mediumblob_col,new_field from t1 where auto > 10; 175Warnings: 176Warning 1265 Data truncated for column 'new_field' at row 2 177Warning 1265 Data truncated for column 'new_field' at row 3 178Warning 1265 Data truncated for column 'new_field' at row 4 179Warning 1265 Data truncated for column 'new_field' at row 5 180Warning 1265 Data truncated for column 'new_field' at row 6 181Warning 1265 Data truncated for column 'new_field' at row 7 182select * from t2; 183auto string mediumblob_col new_field 1841 2 2 ne 1852 0.33333333 ne 1863 -1 -1 ne 1874 -429496729 -4294967295 ne 1885 4294967295 4294967295 ne 1896 hello ne 190select distinct flags from t1; 191flags 192 193one,two,tree 194one 195one,two 196select flags from t1 where find_in_set("two",flags)>0; 197flags 198one,two,tree 199one,two,tree 200one,two 201one,two 202select flags from t1 where find_in_set("unknown",flags)>0; 203flags 204select options,flags from t1 where options="ONE" and flags="ONE"; 205options flags 206one one 207select options,flags from t1 where options="one" and flags="one"; 208options flags 209one one 210drop table t2; 211create table t2 select * from t1; 212update t2 set string="changed" where auto=16; 213show full columns from t1; 214Field Type Collation Null Key Default Extra Privileges Comment 215auto int(5) unsigned NULL NO MUL NULL auto_increment # 216string char(10) latin1_swedish_ci YES newdefault # 217tiny tinyint(4) NULL NO MUL 0 # 218short smallint(6) NULL NO MUL NULL # 219medium mediumint(8) NULL NO MUL 0 # 220long_int int(11) NULL NO 0 # 221longlong bigint(13) NULL NO MUL 0 # 222real_float float(13,1) NULL NO MUL 0.0 # 223real_double double(16,4) NULL YES NULL # 224utiny tinyint(3) unsigned NULL NO 0 # 225ushort smallint(5) unsigned zerofill NULL NO 00000 # 226umedium mediumint(8) unsigned NULL NO MUL 0 # 227ulong int(11) unsigned NULL NO MUL 0 # 228ulonglong bigint(13) unsigned NULL NO MUL 0 # 229time_stamp timestamp NULL NO current_timestamp() on update current_timestamp() # 230date_field char(10) latin1_swedish_ci YES NULL # 231time_field time NULL YES NULL # 232date_time datetime NULL YES NULL # 233new_blob_col varchar(20) latin1_swedish_ci YES NULL # 234tinyblob_col tinyblob NULL YES NULL # 235mediumblob_col mediumblob NULL NO '' # 236options enum('one','two','tree') latin1_swedish_ci NO MUL NULL # 237flags set('one','two','tree') latin1_swedish_ci NO # 238new_field char(10) latin1_swedish_ci NO new # 239show full columns from t2; 240Field Type Collation Null Key Default Extra Privileges Comment 241auto int(5) unsigned NULL NO 0 # 242string char(10) latin1_swedish_ci YES newdefault # 243tiny tinyint(4) NULL NO 0 # 244short smallint(6) NULL NO NULL # 245medium mediumint(8) NULL NO 0 # 246long_int int(11) NULL NO 0 # 247longlong bigint(13) NULL NO 0 # 248real_float float(13,1) NULL NO 0.0 # 249real_double double(16,4) NULL YES NULL # 250utiny tinyint(3) unsigned NULL NO 0 # 251ushort smallint(5) unsigned zerofill NULL NO 00000 # 252umedium mediumint(8) unsigned NULL NO 0 # 253ulong int(11) unsigned NULL NO 0 # 254ulonglong bigint(13) unsigned NULL NO 0 # 255time_stamp timestamp NULL NO current_timestamp() on update current_timestamp() # 256date_field char(10) latin1_swedish_ci YES NULL # 257time_field time NULL YES NULL # 258date_time datetime NULL YES NULL # 259new_blob_col varchar(20) latin1_swedish_ci YES NULL # 260tinyblob_col tinyblob NULL YES NULL # 261mediumblob_col mediumblob NULL NO '' # 262options enum('one','two','tree') latin1_swedish_ci NO NULL # 263flags set('one','two','tree') latin1_swedish_ci NO # 264new_field char(10) latin1_swedish_ci NO new # 265select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null))); 266auto auto 26716 16 268select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and not (t1.string<=>t2.string and t1.tiny<=>t2.tiny and t1.short<=>t2.short and t1.medium<=>t2.medium and t1.long_int<=>t2.long_int and t1.longlong<=>t2.longlong and t1.real_float<=>t2.real_float and t1.real_double<=>t2.real_double and t1.utiny<=>t2.utiny and t1.ushort<=>t2.ushort and t1.umedium<=>t2.umedium and t1.ulong<=>t2.ulong and t1.ulonglong<=>t2.ulonglong and t1.time_stamp<=>t2.time_stamp and t1.date_field<=>t2.date_field and t1.time_field<=>t2.time_field and t1.date_time<=>t2.date_time and t1.new_blob_col<=>t2.new_blob_col and t1.tinyblob_col<=>t2.tinyblob_col and t1.mediumblob_col<=>t2.mediumblob_col and t1.options<=>t2.options and t1.flags<=>t2.flags and t1.new_field<=>t2.new_field); 269auto auto 27016 16 271drop table t2; 272create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1; 273show full columns from t2; 274Field Type Collation Null Key Default Extra Privileges Comment 275auto bigint(11) unsigned NULL NO PRI NULL # 276t1 int(1) NULL NO NULL # 277t2 varchar(1) latin1_swedish_ci NO NULL # 278t3 varchar(256) latin1_swedish_ci YES NULL # 279t4 varbinary(256) NULL YES NULL # 280t5 text latin1_swedish_ci YES NULL # 281t6 blob NULL YES NULL # 282t7 char(0) latin1_swedish_ci NO NULL # 283t8 binary(0) NULL YES NULL # 284select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2; 285t1 t2 length(t3) length(t4) length(t5) length(t6) t7 t8 2861 a 256 256 4096 4096 2871 a 256 256 4096 4096 2881 a 256 256 4096 4096 2891 a 256 256 4096 4096 2901 a 256 256 4096 4096 2911 a 256 256 4096 4096 2921 a 256 256 4096 4096 293drop table t1,t2; 294create table t1 (c int); 295insert into t1 values(1),(2); 296create table t2 select * from t1; 297create table t3 select * from t1, t2; 298ERROR 42S21: Duplicate column name 'c' 299create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2; 300show full columns from t3; 301Field Type Collation Null Key Default Extra Privileges Comment 302c1 int(11) NULL YES NULL # 303c2 int(11) NULL YES NULL # 304const int(1) NULL NO NULL # 305drop table t1,t2,t3; 306create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield)); 307Warnings: 308Note 1831 Duplicate index `myfield_2`. This is deprecated and will be disallowed in a future release 309drop table t1; 310create table t1 ( id integer unsigned not null primary key ); 311create table t2 ( id integer unsigned not null primary key ); 312insert into t1 values (1), (2); 313insert into t2 values (1); 314select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id ); 315id_A id_B 3161 1 3172 NULL 318select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id); 319id_A id_B 3201 1 3212 NULL 322create table t3 (id_A integer unsigned not null, id_B integer unsigned null ); 323insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id ); 324select * from t3; 325id_A id_B 3261 1 3272 NULL 328truncate table t3; 329insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id); 330select * from t3; 331id_A id_B 3321 1 3332 NULL 334drop table t3; 335create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id ); 336select * from t3; 337id_A id_B 3381 1 3392 NULL 340drop table t3; 341create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id); 342select * from t3; 343id_A id_B 3441 1 3452 NULL 346drop table t1,t2,t3; 347