1set default_storage_engine='tokudb'; 2drop table if exists t1, t2; 3create table t1 (v varchar(30), c char(3), e enum('abc','def','ghi'), t text); 4truncate table vchar; 5show create table t1; 6Table Create Table 7t1 CREATE TABLE `t1` ( 8 `v` varchar(30) DEFAULT NULL, 9 `c` char(3) DEFAULT NULL, 10 `e` enum('abc','def','ghi') DEFAULT NULL, 11 `t` text DEFAULT NULL 12) ENGINE=ENGINE DEFAULT CHARSET=latin1 13show create table vchar; 14Table Create Table 15vchar CREATE TABLE `vchar` ( 16 `v` varchar(30) DEFAULT NULL, 17 `c` char(3) DEFAULT NULL, 18 `e` enum('abc','def','ghi') DEFAULT NULL, 19 `t` text DEFAULT NULL 20) ENGINE=ENGINE DEFAULT CHARSET=latin1 21insert into t1 values ('abc', 'de', 'ghi', 'jkl'); 22insert into t1 values ('abc ', 'de ', 'ghi', 'jkl '); 23insert into t1 values ('abc ', 'd ', 'ghi', 'jkl '); 24insert into vchar values ('abc', 'de', 'ghi', 'jkl'); 25insert into vchar values ('abc ', 'de ', 'ghi', 'jkl '); 26insert into vchar values ('abc ', 'd ', 'ghi', 'jkl '); 27select length(v),length(c),length(e),length(t) from t1; 28length(v) length(c) length(e) length(t) 293 2 3 3 304 2 3 4 317 1 3 7 32select length(v),length(c),length(e),length(t) from vchar; 33length(v) length(c) length(e) length(t) 343 2 3 3 353 2 3 4 363 1 3 7 37alter table vchar add i int; 38show create table vchar; 39Table Create Table 40vchar CREATE TABLE `vchar` ( 41 `v` varchar(30) DEFAULT NULL, 42 `c` char(3) DEFAULT NULL, 43 `e` enum('abc','def','ghi') DEFAULT NULL, 44 `t` text DEFAULT NULL, 45 `i` int(11) DEFAULT NULL 46) ENGINE=ENGINE DEFAULT CHARSET=latin1 47select length(v),length(c),length(e),length(t) from vchar; 48length(v) length(c) length(e) length(t) 493 2 3 3 503 2 3 4 513 1 3 7 52drop table t1, vchar; 53create table t1 (v varchar(20)); 54insert into t1 values('a '); 55select v='a' from t1; 56v='a' 571 58select binary v='a' from t1; 59binary v='a' 600 61select binary v='a ' from t1; 62binary v='a ' 631 64insert into t1 values('a'); 65alter table t1 add primary key (v); 66ERROR 23000: Duplicate entry 'a' for key 'PRIMARY' 67drop table t1; 68create table t1 (v varbinary(20)); 69insert into t1 values('a'); 70insert into t1 values('a '); 71alter table t1 add primary key (v); 72drop table t1; 73create table t1 (v varchar(254), index (v)); 74insert into t1 values ("This is a test "); 75insert into t1 values ("Some sample data"); 76insert into t1 values (" garbage "); 77insert into t1 values (" This is a test "); 78insert into t1 values ("This is a test"); 79insert into t1 values ("Hello world"); 80insert into t1 values ("Foo bar"); 81insert into t1 values ("This is a test"); 82insert into t1 values ("MySQL varchar test"); 83insert into t1 values ("test MySQL varchar"); 84insert into t1 values ("This is a long string to have some random length data included"); 85insert into t1 values ("Short string"); 86insert into t1 values ("VSS"); 87insert into t1 values ("Some samples"); 88insert into t1 values ("Bar foo"); 89insert into t1 values ("Bye"); 90select * from t1 where v like 'This is a test' order by v; 91v 92This is a test 93This is a test 94select * from t1 where v='This is a test' order by v; 95v 96This is a test 97This is a test 98This is a test 99select * from t1 where v like 'S%' order by v; 100v 101Short string 102Some sample data 103Some samples 104explain select * from t1 where v like 'This is a test' order by v; 105id select_type table type possible_keys key key_len ref rows Extra 1061 SIMPLE t1 range v v 257 NULL NA Using where; Using index 107explain select * from t1 where v='This is a test' order by v; 108id select_type table type possible_keys key key_len ref rows Extra 1091 SIMPLE t1 ref v v 257 const NA Using where; Using index 110explain select * from t1 where v like 'S%' order by v; 111id select_type table type possible_keys key key_len ref rows Extra 1121 SIMPLE t1 range v v 257 NULL NA Using where; Using index 113alter table t1 change v v varchar(255); 114select * from t1 where v like 'This is a test' order by v; 115v 116This is a test 117This is a test 118select * from t1 where v='This is a test' order by v; 119v 120This is a test 121This is a test 122This is a test 123select * from t1 where v like 'S%' order by v; 124v 125Short string 126Some sample data 127Some samples 128explain select * from t1 where v like 'This is a test' order by v; 129id select_type table type possible_keys key key_len ref rows Extra 1301 SIMPLE t1 range v v 258 NULL NA Using where; Using index 131explain select * from t1 where v='This is a test' order by v; 132id select_type table type possible_keys key key_len ref rows Extra 1331 SIMPLE t1 ref v v 258 const NA Using where; Using index 134explain select * from t1 where v like 'S%' order by v; 135id select_type table type possible_keys key key_len ref rows Extra 1361 SIMPLE t1 range v v 258 NULL NA Using where; Using index 137alter table t1 change v v varchar(256); 138select * from t1 where v like 'This is a test' order by v; 139v 140This is a test 141This is a test 142select * from t1 where v='This is a test' order by v; 143v 144This is a test 145This is a test 146This is a test 147select * from t1 where v like 'S%' order by v; 148v 149Short string 150Some sample data 151Some samples 152explain select * from t1 where v like 'This is a test' order by v; 153id select_type table type possible_keys key key_len ref rows Extra 1541 SIMPLE t1 range v v 259 NULL NA Using where; Using index 155explain select * from t1 where v='This is a test' order by v; 156id select_type table type possible_keys key key_len ref rows Extra 1571 SIMPLE t1 ref v v 259 const NA Using where; Using index 158explain select * from t1 where v like 'S%' order by v; 159id select_type table type possible_keys key key_len ref rows Extra 1601 SIMPLE t1 range v v 259 NULL NA Using where; Using index 161alter table t1 change v v varchar(257); 162select * from t1 where v like 'This is a test' order by v; 163v 164This is a test 165This is a test 166select * from t1 where v='This is a test' order by v; 167v 168This is a test 169This is a test 170This is a test 171select * from t1 where v like 'S%' order by v; 172v 173Short string 174Some sample data 175Some samples 176explain select * from t1 where v like 'This is a test' order by v; 177id select_type table type possible_keys key key_len ref rows Extra 1781 SIMPLE t1 range v v 260 NULL NA Using where; Using index 179explain select * from t1 where v='This is a test' order by v; 180id select_type table type possible_keys key key_len ref rows Extra 1811 SIMPLE t1 ref v v 260 const NA Using where; Using index 182explain select * from t1 where v like 'S%' order by v; 183id select_type table type possible_keys key key_len ref rows Extra 1841 SIMPLE t1 range v v 260 NULL NA Using where; Using index 185alter table t1 change v v varchar(258); 186select * from t1 where v like 'This is a test' order by v; 187v 188This is a test 189This is a test 190select * from t1 where v='This is a test' order by v; 191v 192This is a test 193This is a test 194This is a test 195select * from t1 where v like 'S%' order by v; 196v 197Short string 198Some sample data 199Some samples 200explain select * from t1 where v like 'This is a test' order by v; 201id select_type table type possible_keys key key_len ref rows Extra 2021 SIMPLE t1 range v v 261 NULL NA Using where; Using index 203explain select * from t1 where v='This is a test' order by v; 204id select_type table type possible_keys key key_len ref rows Extra 2051 SIMPLE t1 ref v v 261 const NA Using where; Using index 206explain select * from t1 where v like 'S%' order by v; 207id select_type table type possible_keys key key_len ref rows Extra 2081 SIMPLE t1 range v v 261 NULL NA Using where; Using index 209alter table t1 change v v varchar(259); 210select * from t1 where v like 'This is a test' order by v; 211v 212This is a test 213This is a test 214select * from t1 where v='This is a test' order by v; 215v 216This is a test 217This is a test 218This is a test 219select * from t1 where v like 'S%' order by v; 220v 221Short string 222Some sample data 223Some samples 224explain select * from t1 where v like 'This is a test' order by v; 225id select_type table type possible_keys key key_len ref rows Extra 2261 SIMPLE t1 range v v 262 NULL NA Using where; Using index 227explain select * from t1 where v='This is a test' order by v; 228id select_type table type possible_keys key key_len ref rows Extra 2291 SIMPLE t1 ref v v 262 const NA Using where; Using index 230explain select * from t1 where v like 'S%' order by v; 231id select_type table type possible_keys key key_len ref rows Extra 2321 SIMPLE t1 range v v 262 NULL NA Using where; Using index 233alter table t1 change v v varchar(258); 234select * from t1 where v like 'This is a test' order by v; 235v 236This is a test 237This is a test 238select * from t1 where v='This is a test' order by v; 239v 240This is a test 241This is a test 242This is a test 243select * from t1 where v like 'S%' order by v; 244v 245Short string 246Some sample data 247Some samples 248explain select * from t1 where v like 'This is a test' order by v; 249id select_type table type possible_keys key key_len ref rows Extra 2501 SIMPLE t1 range v v 261 NULL NA Using where; Using index 251explain select * from t1 where v='This is a test' order by v; 252id select_type table type possible_keys key key_len ref rows Extra 2531 SIMPLE t1 ref v v 261 const NA Using where; Using index 254explain select * from t1 where v like 'S%' order by v; 255id select_type table type possible_keys key key_len ref rows Extra 2561 SIMPLE t1 range v v 261 NULL NA Using where; Using index 257alter table t1 change v v varchar(257); 258select * from t1 where v like 'This is a test' order by v; 259v 260This is a test 261This is a test 262select * from t1 where v='This is a test' order by v; 263v 264This is a test 265This is a test 266This is a test 267select * from t1 where v like 'S%' order by v; 268v 269Short string 270Some sample data 271Some samples 272explain select * from t1 where v like 'This is a test' order by v; 273id select_type table type possible_keys key key_len ref rows Extra 2741 SIMPLE t1 range v v 260 NULL NA Using where; Using index 275explain select * from t1 where v='This is a test' order by v; 276id select_type table type possible_keys key key_len ref rows Extra 2771 SIMPLE t1 ref v v 260 const NA Using where; Using index 278explain select * from t1 where v like 'S%' order by v; 279id select_type table type possible_keys key key_len ref rows Extra 2801 SIMPLE t1 range v v 260 NULL NA Using where; Using index 281alter table t1 change v v varchar(256); 282select * from t1 where v like 'This is a test' order by v; 283v 284This is a test 285This is a test 286select * from t1 where v='This is a test' order by v; 287v 288This is a test 289This is a test 290This is a test 291select * from t1 where v like 'S%' order by v; 292v 293Short string 294Some sample data 295Some samples 296explain select * from t1 where v like 'This is a test' order by v; 297id select_type table type possible_keys key key_len ref rows Extra 2981 SIMPLE t1 range v v 259 NULL NA Using where; Using index 299explain select * from t1 where v='This is a test' order by v; 300id select_type table type possible_keys key key_len ref rows Extra 3011 SIMPLE t1 ref v v 259 const NA Using where; Using index 302explain select * from t1 where v like 'S%' order by v; 303id select_type table type possible_keys key key_len ref rows Extra 3041 SIMPLE t1 range v v 259 NULL NA Using where; Using index 305alter table t1 change v v varchar(255); 306select * from t1 where v like 'This is a test' order by v; 307v 308This is a test 309This is a test 310select * from t1 where v='This is a test' order by v; 311v 312This is a test 313This is a test 314This is a test 315select * from t1 where v like 'S%' order by v; 316v 317Short string 318Some sample data 319Some samples 320explain select * from t1 where v like 'This is a test' order by v; 321id select_type table type possible_keys key key_len ref rows Extra 3221 SIMPLE t1 range v v 258 NULL NA Using where; Using index 323explain select * from t1 where v='This is a test' order by v; 324id select_type table type possible_keys key key_len ref rows Extra 3251 SIMPLE t1 ref v v 258 const NA Using where; Using index 326explain select * from t1 where v like 'S%' order by v; 327id select_type table type possible_keys key key_len ref rows Extra 3281 SIMPLE t1 range v v 258 NULL NA Using where; Using index 329alter table t1 change v v varchar(254); 330select * from t1 where v like 'This is a test' order by v; 331v 332This is a test 333This is a test 334select * from t1 where v='This is a test' order by v; 335v 336This is a test 337This is a test 338This is a test 339select * from t1 where v like 'S%' order by v; 340v 341Short string 342Some sample data 343Some samples 344explain select * from t1 where v like 'This is a test' order by v; 345id select_type table type possible_keys key key_len ref rows Extra 3461 SIMPLE t1 range v v 257 NULL NA Using where; Using index 347explain select * from t1 where v='This is a test' order by v; 348id select_type table type possible_keys key key_len ref rows Extra 3491 SIMPLE t1 ref v v 257 const NA Using where; Using index 350explain select * from t1 where v like 'S%' order by v; 351id select_type table type possible_keys key key_len ref rows Extra 3521 SIMPLE t1 range v v 257 NULL NA Using where; Using index 353alter table t1 change v v varchar(253); 354alter table t1 change v v varchar(254), drop key v; 355alter table t1 change v v varchar(300), add key (v(10)); 356select * from t1 where v like 'This is a test' order by v; 357v 358This is a test 359This is a test 360select * from t1 where v='This is a test' order by v; 361v 362This is a test 363This is a test 364This is a test 365select * from t1 where v like 'S%' order by v; 366v 367Short string 368Some sample data 369Some samples 370explain select * from t1 where v like 'This is a test' order by v; 371id select_type table type possible_keys key key_len ref rows Extra 3721 SIMPLE t1 range v v 13 NULL NA Using where; Using filesort 373explain select * from t1 where v='This is a test' order by v; 374id select_type table type possible_keys key key_len ref rows Extra 3751 SIMPLE t1 ref v v 13 const NA Using where 376explain select * from t1 where v like 'S%' order by v; 377id select_type table type possible_keys key key_len ref rows Extra 3781 SIMPLE t1 range v v 13 NULL NA Using where; Using filesort 379drop table t1; 380create table t1 (pkcol varchar(16), othercol varchar(16), primary key (pkcol)); 381insert into t1 values ('test', 'something'); 382update t1 set othercol='somethingelse' where pkcol='test'; 383select * from t1; 384pkcol othercol 385test somethingelse 386drop table t1; 387create table t1 (a int, b varchar(12)); 388insert into t1 values (1, 'A'), (22, NULL); 389create table t2 (a int); 390insert into t2 values (22), (22); 391select t1.a, t1.b, min(t1.b) from t1 inner join t2 ON t2.a = t1.a 392group by t1.b, t1.a; 393a b min(t1.b) 39422 NULL NULL 395drop table t1, t2; 396create table t1 (f1 varchar(65500)); 397create index index1 on t1(f1(10)); 398show create table t1; 399Table Create Table 400t1 CREATE TABLE `t1` ( 401 `f1` varchar(65500) DEFAULT NULL, 402 KEY `index1` (`f1`(10)) 403) ENGINE=ENGINE DEFAULT CHARSET=latin1 404alter table t1 modify f1 varchar(255); 405show create table t1; 406Table Create Table 407t1 CREATE TABLE `t1` ( 408 `f1` varchar(255) DEFAULT NULL, 409 KEY `index1` (`f1`(10)) 410) ENGINE=ENGINE DEFAULT CHARSET=latin1 411alter table t1 modify f1 tinytext; 412show create table t1; 413Table Create Table 414t1 CREATE TABLE `t1` ( 415 `f1` tinytext DEFAULT NULL, 416 KEY `index1` (`f1`(10)) 417) ENGINE=ENGINE DEFAULT CHARSET=latin1 418drop table t1; 419DROP TABLE IF EXISTS t1; 420CREATE TABLE t1(f1 VARCHAR(100) DEFAULT 'test'); 421INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3)); 422DROP TABLE IF EXISTS t1; 423CREATE TABLE t1(f1 CHAR(100) DEFAULT 'test'); 424INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3)); 425DROP TABLE IF EXISTS t1; 426CREATE TABLE t1 (a CHAR(2)); 427INSERT INTO t1 VALUES (10), (50), (30), ('1a'), (60), ('t'); 428SELECT a,(a + 0) FROM t1 ORDER BY a; 429a (a + 0) 43010 10 4311a 1 43230 30 43350 50 43460 60 435t 0 436Warnings: 437Warning 1292 Truncated incorrect DOUBLE value: '1a' 438Warning 1292 Truncated incorrect DOUBLE value: 't ' 439SELECT a,(a DIV 2) FROM t1 ORDER BY a; 440a (a DIV 2) 44110 5 4421a 0 44330 15 44450 25 44560 30 446t 0 447Warnings: 448Warning 1292 Truncated incorrect DECIMAL value: '1a' 449Warning 1918 Encountered illegal value '' when converting to DECIMAL 450Warning 1292 Truncated incorrect DECIMAL value: 't ' 451SELECT a,CAST(a AS SIGNED) FROM t1 ORDER BY a; 452a CAST(a AS SIGNED) 45310 10 4541a 1 45530 30 45650 50 45760 60 458t 0 459Warnings: 460Warning 1292 Truncated incorrect INTEGER value: '1a' 461Warning 1292 Truncated incorrect INTEGER value: 't' 462DROP TABLE t1; 463CREATE TABLE t1 (a VARCHAR(16)); 464INSERT INTO t1 VALUES ('5'), ('s'), (''); 465SELECT 5 = a FROM t1; 4665 = a 4671 4680 4690 470Warnings: 471Warning 1292 Truncated incorrect DOUBLE value: 's' 472Warning 1292 Truncated incorrect DOUBLE value: '' 473DROP TABLE t1; 474CREATE TABLE t1 (a CHAR(16)); 475INSERT INTO t1 VALUES ('5'), ('s'), (''); 476SELECT 5 = a FROM t1; 4775 = a 4781 4790 4800 481Warnings: 482Warning 1292 Truncated incorrect DOUBLE value: 's ' 483Warning 1292 Truncated incorrect DOUBLE value: ' ' 484DROP TABLE t1; 485