show variables like 'character_sets_dir%';
Variable_name	Value
character_sets_dir	MYSQL_TEST_DIR/std_data/
set names utf8;
select * from information_schema.collations where id>256 order by id;
COLLATION_NAME	CHARACTER_SET_NAME	ID	IS_DEFAULT	IS_COMPILED	SORTLEN
utf8mb4_test_ci	utf8mb4	326			8
utf16_test_ci	utf16	327			8
utf8mb4_test_400_ci	utf8mb4	328			8
utf8_bengali_standard_ci	utf8	336			8
utf8_bengali_traditional_ci	utf8	337			8
utf8_phone_ci	utf8	352			8
utf8_test_ci	utf8	353			8
utf8_5624_1	utf8	354			8
utf8_5624_2	utf8	355			8
utf8_5624_3	utf8	356			8
utf8_5624_4	utf8	357			8
ucs2_test_ci	ucs2	358			8
ucs2_vn_ci	ucs2	359			8
ucs2_5624_1	ucs2	360			8
utf8_5624_5	utf8	368			8
utf32_test_ci	utf32	391			8
utf8_maxuserid_ci	utf8	2047			8
show collation like '%test%';
Collation	Charset	Id	Default	Compiled	Sortlen
latin1_test	latin1	99		Yes	1
utf8_test_ci	utf8	353			8
ucs2_test_ci	ucs2	358			8
utf8mb4_test_ci	utf8mb4	326			8
utf8mb4_test_400_ci	utf8mb4	328			8
utf16_test_ci	utf16	327			8
utf32_test_ci	utf32	391			8
show collation like 'ucs2_vn_ci';
Collation	Charset	Id	Default	Compiled	Sortlen
ucs2_vn_ci	ucs2	359			8
create table 2byte_collation (c1 char(1) character set ucs2 collate ucs2_vn_ci)
engine = InnoDB;
insert into 2byte_collation values (0x0061);
set @@character_set_results=NULL;
select * from 2byte_collation;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	2byte_collation	2byte_collation	c1	c1	254	2	2	Y	0	0	359
c1
a
drop table 2byte_collation;
CREATE TABLE 2byte_collation (s1 char(10) character set utf8 collate utf8_maxuserid_ci) engine = innodb;
INSERT INTO 2byte_collation VALUES ('a'),('b');
SELECT * FROM 2byte_collation WHERE s1='a' ORDER BY BINARY s1;
s1
a
b
DROP TABLE 2byte_collation;
SET @innodb_change_buffering_debug_orig = @@innodb_change_buffering_debug;
SET GLOBAL innodb_change_buffering_debug = 1;
set names utf8;
show collation like 'utf8_maxuserid_ci';
Collation	Charset	Id	Default	Compiled	Sortlen
utf8_maxuserid_ci	utf8	2047			8
CREATE TABLE 2byte_collation(
a INT AUTO_INCREMENT PRIMARY KEY,
b CHAR(100) character set utf8 collate utf8_maxuserid_ci,
c INT,
z INT,
INDEX(b))
ENGINE=InnoDB STATS_PERSISTENT=0;
INSERT INTO 2byte_collation VALUES(0,'x',1, 1);
CREATE UNIQUE INDEX idx3 ON 2byte_collation(c, b);
INSERT INTO 2byte_collation SELECT 0,b,c+1,z+1 FROM 2byte_collation;
INSERT INTO 2byte_collation SELECT 0,b,c+10,z+10 FROM 2byte_collation;
INSERT INTO 2byte_collation SELECT 0,b,c+20,z+20 FROM 2byte_collation;
INSERT INTO 2byte_collation SELECT 0,b,c+50,z+50 FROM 2byte_collation;
INSERT INTO 2byte_collation SELECT 0,b,c+100,z+100 FROM 2byte_collation;
INSERT INTO 2byte_collation SELECT 0,b,c+200,z+200 FROM 2byte_collation;
INSERT INTO 2byte_collation SELECT 0,b,c+400,z+400 FROM 2byte_collation;
INSERT INTO 2byte_collation SELECT 0,b,c+800,z+800 FROM 2byte_collation;
INSERT INTO 2byte_collation SELECT 0,b,c+1600,z+1600 FROM 2byte_collation;
INSERT INTO 2byte_collation SELECT 0,b,c+4000,z+4000 FROM 2byte_collation;
CREATE INDEX idx5 ON 2byte_collation(b, c);
SELECT b FROM 2byte_collation LIMIT 10;
b
x
x
x
x
x
x
x
x
x
x
INSERT INTO 2byte_collation VALUES (10001, "a", 20001, 20001);
UPDATE 2byte_collation set b = "aaa" where c = 20001;
DROP TABLE 2byte_collation;
SET GLOBAL innodb_change_buffering_debug = 0;