drop table if exists t1; set names sjis; select 'a' like 'a'; 'a' like 'a' 1 select 'A' like 'a'; 'A' like 'a' 1 select 'A' like 'a' collate sjis_bin; 'A' like 'a' collate sjis_bin 0 set @sjis1= _sjis 0xa1a2a3a4a5a6a7a8a9aaabacadaeaf; set @sjis2= _sjis 0xb0b1b2b3b4b5b6b7b8b9babbbcbdbebf; set @sjis3= _sjis 0xc0c1c2c3c4c5c6c7c8c9cacbcccdcecf; set @sjis4= _sjis 0xd0d1d2d3d4d5d6d7d8d9dadbdcdddedf; set @utf81= CONVERT(@sjis1 USING utf8); set @utf82= CONVERT(@sjis2 USING utf8); set @utf83= CONVERT(@sjis3 USING utf8); set @utf84= CONVERT(@sjis4 USING utf8); select hex(@utf81); hex(@utf81) EFBDA1EFBDA2EFBDA3EFBDA4EFBDA5EFBDA6EFBDA7EFBDA8EFBDA9EFBDAAEFBDABEFBDACEFBDADEFBDAEEFBDAF select hex(@utf82); hex(@utf82) EFBDB0EFBDB1EFBDB2EFBDB3EFBDB4EFBDB5EFBDB6EFBDB7EFBDB8EFBDB9EFBDBAEFBDBBEFBDBCEFBDBDEFBDBEEFBDBF select hex(@utf83); hex(@utf83) EFBE80EFBE81EFBE82EFBE83EFBE84EFBE85EFBE86EFBE87EFBE88EFBE89EFBE8AEFBE8BEFBE8CEFBE8DEFBE8EEFBE8F select hex(@utf84); hex(@utf84) EFBE90EFBE91EFBE92EFBE93EFBE94EFBE95EFBE96EFBE97EFBE98EFBE99EFBE9AEFBE9BEFBE9CEFBE9DEFBE9EEFBE9F select hex(CONVERT(@utf81 USING sjis)); hex(CONVERT(@utf81 USING sjis)) A1A2A3A4A5A6A7A8A9AAABACADAEAF select hex(CONVERT(@utf82 USING sjis)); hex(CONVERT(@utf82 USING sjis)) B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF select hex(CONVERT(@utf83 USING sjis)); hex(CONVERT(@utf83 USING sjis)) C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF select hex(CONVERT(@utf84 USING sjis)); hex(CONVERT(@utf84 USING sjis)) D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF create table t1 (a char(10) character set sjis); insert into t1 values (0x878A); select hex(a) from t1; hex(a) 878A drop table t1; create table t1(c enum(0x9353,0x9373) character set sjis); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `c` enum('S','s') CHARACTER SET sjis DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values (0x9353); insert into t1 values (0x9373); select hex(c) from t1; hex(c) 9353 9373 drop table t1; SET NAMES sjis; CREATE TABLE t1 ( c char(16) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=sjis; insert into t1 values(0xb1),(0xb2),(0xb3); select hex(c) from t1; hex(c) B1 B2 B3 drop table t1; SET collation_connection='sjis_japanese_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) sjis_japanese_ci 6109 sjis_japanese_ci 61 sjis_japanese_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) sjis_japanese_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 sjis_japanese_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='sjis_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) sjis_bin 6109 sjis_bin 61 sjis_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) sjis_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 sjis_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 sjis; SELECT HEX('@\') FROM DUAL; HEX('@\') 8DB2939181408C5C # Start of 5.1 tests Bug#44352 UPPER/LOWER function doesn't work correctly on cp932 and sjis environment. CREATE TABLE t1 (a varchar(16)) character set sjis; INSERT INTO t1 VALUES (0x8372835E),(0x8352835E); SELECT hex(a), hex(lower(a)), hex(upper(a)) FROM t1 ORDER BY binary(a); hex(a) hex(lower(a)) hex(upper(a)) 8352835E 8352835E 8352835E 8372835E 8372835E 8372835E DROP TABLE t1; # End of 5.1 tests