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('揝','搒') 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
