drop table if exists t1; SET @test_character_set= 'gb2312'; SET @test_collation= 'gb2312_chinese_ci'; SET @safe_character_set_server= @@character_set_server; SET @safe_collation_server= @@collation_server; SET @safe_character_set_client= @@character_set_client; SET @safe_character_set_results= @@character_set_results; SET character_set_server= @test_character_set; SET collation_server= @test_collation; CREATE DATABASE d1; USE d1; CREATE TABLE t1 (c CHAR(10), KEY(c)); SHOW FULL COLUMNS FROM t1; Field Type Collation Null Key Default Extra Privileges Comment c char(10) gb2312_chinese_ci YES MUL NULL INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa'); SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%'; want3results aaa aaaa aaaaa DROP TABLE t1; CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2))); SHOW FULL COLUMNS FROM t1; Field Type Collation Null Key Default Extra Privileges Comment c1 varchar(15) gb2312_chinese_ci YES MUL NULL INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab'); SELECT c1 as want3results from t1 where c1 like 'l%'; want3results location loberge lotre SELECT c1 as want3results from t1 where c1 like 'lo%'; want3results location loberge lotre SELECT c1 as want1result from t1 where c1 like 'loc%'; want1result location SELECT c1 as want1result from t1 where c1 like 'loca%'; want1result location SELECT c1 as want1result from t1 where c1 like 'locat%'; want1result location SELECT c1 as want1result from t1 where c1 like 'locati%'; want1result location SELECT c1 as want1result from t1 where c1 like 'locatio%'; want1result location SELECT c1 as want1result from t1 where c1 like 'location%'; want1result location DROP TABLE t1; create table t1 (a set('a') not null); insert into t1 values (),(); Warnings: Warning 1364 Field 'a' doesn't have a default value select cast(a as char(1)) from t1; cast(a as char(1)) select a sounds like a from t1; a sounds like a 1 1 select 1 from t1 order by cast(a as char(1)); 1 1 1 drop table t1; set names utf8; create table t1 ( name varchar(10), level smallint unsigned); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `name` varchar(10) DEFAULT NULL, `level` smallint(5) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=gb2312 insert into t1 values ('string',1); select concat(name,space(level)), concat(name, repeat(' ',level)) from t1; concat(name,space(level)) concat(name, repeat(' ',level)) string string drop table t1; DROP DATABASE d1; USE test; SET character_set_server= @safe_character_set_server; SET collation_server= @safe_collation_server; SET character_set_client= @safe_character_set_client; SET character_set_results= @safe_character_set_results; SET NAMES gb2312; SET collation_connection='gb2312_chinese_ci'; create table t1 select repeat('a',4000) a; delete from t1; insert into t1 values ('a'), ('a '), ('a\t'); select collation(a),hex(a) from t1 order by a; collation(a) hex(a) gb2312_chinese_ci 6109 gb2312_chinese_ci 61 gb2312_chinese_ci 6120 drop table t1; create table t1 engine=innodb select repeat('a',50) as c1; alter table t1 add index(c1(5)); insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111'); select collation(c1) from t1 limit 1; collation(c1) gb2312_chinese_ci select c1 from t1 where c1 like 'abcdef%' order by c1; c1 abcdefg select c1 from t1 where c1 like 'abcde1%' order by c1; c1 abcde100 abcde110 abcde111 select c1 from t1 where c1 like 'abcde11%' order by c1; c1 abcde110 abcde111 select c1 from t1 where c1 like 'abcde111%' order by c1; c1 abcde111 drop table t1; select @@collation_connection; @@collation_connection gb2312_chinese_ci create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ; insert into t1 values('abcdef'); insert into t1 values('_bcdef'); insert into t1 values('a_cdef'); insert into t1 values('ab_def'); insert into t1 values('abc_ef'); insert into t1 values('abcd_f'); insert into t1 values('abcde_'); select c1 as c1u from t1 where c1 like 'ab\_def'; c1u ab_def select c1 as c2h from t1 where c1 like 'ab#_def' escape '#'; c2h ab_def drop table t1; CREATE TABLE t1 AS SELECT 10 AS a, REPEAT('a',20) AS b, REPEAT('a',8) AS c, REPEAT('a',8) AS d; ALTER TABLE t1 ADD PRIMARY KEY(a), ADD KEY(b); INSERT INTO t1 (a, b) VALUES (1, repeat(0xF1F2,5)); INSERT INTO t1 (a, b) VALUES (2, repeat(0xF1F2,10)); INSERT INTO t1 (a, b) VALUES (3, repeat(0xF1F2,11)); INSERT INTO t1 (a, b) VALUES (4, repeat(0xF1F2,12)); SELECT hex(concat(repeat(0xF1F2, 10), '%')); hex(concat(repeat(0xF1F2, 10), '%')) F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F225 3 rows expected SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%'); a hex(b) c 2 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2 3 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2 4 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2 DROP TABLE t1; SET collation_connection='gb2312_bin'; create table t1 select repeat('a',4000) a; delete from t1; insert into t1 values ('a'), ('a '), ('a\t'); select collation(a),hex(a) from t1 order by a; collation(a) hex(a) gb2312_bin 6109 gb2312_bin 61 gb2312_bin 6120 drop table t1; create table t1 engine=innodb select repeat('a',50) as c1; alter table t1 add index(c1(5)); insert into t1 values ('abcdefg'),('abcde100'),('abcde110'),('abcde111'); select collation(c1) from t1 limit 1; collation(c1) gb2312_bin select c1 from t1 where c1 like 'abcdef%' order by c1; c1 abcdefg select c1 from t1 where c1 like 'abcde1%' order by c1; c1 abcde100 abcde110 abcde111 select c1 from t1 where c1 like 'abcde11%' order by c1; c1 abcde110 abcde111 select c1 from t1 where c1 like 'abcde111%' order by c1; c1 abcde111 drop table t1; select @@collation_connection; @@collation_connection gb2312_bin create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ; insert into t1 values('abcdef'); insert into t1 values('_bcdef'); insert into t1 values('a_cdef'); insert into t1 values('ab_def'); insert into t1 values('abc_ef'); insert into t1 values('abcd_f'); insert into t1 values('abcde_'); select c1 as c1u from t1 where c1 like 'ab\_def'; c1u ab_def select c1 as c2h from t1 where c1 like 'ab#_def' escape '#'; c2h ab_def drop table t1; CREATE TABLE t1 AS SELECT 10 AS a, REPEAT('a',20) AS b, REPEAT('a',8) AS c, REPEAT('a',8) AS d; ALTER TABLE t1 ADD PRIMARY KEY(a), ADD KEY(b); INSERT INTO t1 (a, b) VALUES (1, repeat(0xF1F2,5)); INSERT INTO t1 (a, b) VALUES (2, repeat(0xF1F2,10)); INSERT INTO t1 (a, b) VALUES (3, repeat(0xF1F2,11)); INSERT INTO t1 (a, b) VALUES (4, repeat(0xF1F2,12)); SELECT hex(concat(repeat(0xF1F2, 10), '%')); hex(concat(repeat(0xF1F2, 10), '%')) F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F225 3 rows expected SELECT a, hex(b), c FROM t1 WHERE b LIKE concat(repeat(0xF1F2,10), '%'); a hex(b) c 2 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2 3 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2 4 F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2F1F2 DROP TABLE t1; SET NAMES gb2312; CREATE TABLE t1 (a text) character set gb2312; INSERT INTO t1 VALUES (0xA2A1),(0xD7FE); SELECT hex(a) FROM t1 ORDER BY a; hex(a) A2A1 D7FE DROP TABLE t1;