show variables like 'character_sets_dir%';
Variable_name	Value
character_sets_dir	MYSQL_TEST_DIR/std_data/
set names utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
select collation_name, character_set_name, id from information_schema.collations where id>256 order by id;
collation_name	character_set_name	id
utf8mb4_is_0900_ai_ci	utf8mb4	257
utf8mb4_lv_0900_ai_ci	utf8mb4	258
utf8mb4_ro_0900_ai_ci	utf8mb4	259
utf8mb4_sl_0900_ai_ci	utf8mb4	260
utf8mb4_pl_0900_ai_ci	utf8mb4	261
utf8mb4_et_0900_ai_ci	utf8mb4	262
utf8mb4_es_0900_ai_ci	utf8mb4	263
utf8mb4_sv_0900_ai_ci	utf8mb4	264
utf8mb4_tr_0900_ai_ci	utf8mb4	265
utf8mb4_cs_0900_ai_ci	utf8mb4	266
utf8mb4_da_0900_ai_ci	utf8mb4	267
utf8mb4_lt_0900_ai_ci	utf8mb4	268
utf8mb4_sk_0900_ai_ci	utf8mb4	269
utf8mb4_es_trad_0900_ai_ci	utf8mb4	270
utf8mb4_la_0900_ai_ci	utf8mb4	271
utf8mb4_eo_0900_ai_ci	utf8mb4	273
utf8mb4_hu_0900_ai_ci	utf8mb4	274
utf8mb4_hr_0900_ai_ci	utf8mb4	275
utf8mb4_vi_0900_ai_ci	utf8mb4	277
utf8mb4_0900_as_cs	utf8mb4	278
utf8mb4_de_pb_0900_as_cs	utf8mb4	279
utf8mb4_is_0900_as_cs	utf8mb4	280
utf8mb4_lv_0900_as_cs	utf8mb4	281
utf8mb4_ro_0900_as_cs	utf8mb4	282
utf8mb4_sl_0900_as_cs	utf8mb4	283
utf8mb4_pl_0900_as_cs	utf8mb4	284
utf8mb4_et_0900_as_cs	utf8mb4	285
utf8mb4_es_0900_as_cs	utf8mb4	286
utf8mb4_sv_0900_as_cs	utf8mb4	287
utf8mb4_tr_0900_as_cs	utf8mb4	288
utf8mb4_cs_0900_as_cs	utf8mb4	289
utf8mb4_da_0900_as_cs	utf8mb4	290
utf8mb4_lt_0900_as_cs	utf8mb4	291
utf8mb4_sk_0900_as_cs	utf8mb4	292
utf8mb4_es_trad_0900_as_cs	utf8mb4	293
utf8mb4_la_0900_as_cs	utf8mb4	294
utf8mb4_eo_0900_as_cs	utf8mb4	296
utf8mb4_hu_0900_as_cs	utf8mb4	297
utf8mb4_hr_0900_as_cs	utf8mb4	298
utf8mb4_vi_0900_as_cs	utf8mb4	300
utf8mb4_ja_0900_as_cs	utf8mb4	303
utf8mb4_ja_0900_as_cs_ks	utf8mb4	304
utf8mb4_0900_as_ci	utf8mb4	305
utf8mb4_ru_0900_ai_ci	utf8mb4	306
utf8mb4_ru_0900_as_cs	utf8mb4	307
utf8mb4_zh_0900_as_cs	utf8mb4	308
utf8mb4_0900_bin	utf8mb4	309
utf8mb4_test_ci	utf8mb4	326
utf16_test_ci	utf16	327
utf8mb4_test_400_ci	utf8mb4	328
utf8_bengali_standard_ci	utf8	336
utf8_bengali_traditional_ci	utf8	337
utf8_phone_ci	utf8	352
utf8_test_ci	utf8	353
utf8_5624_1	utf8	354
utf8_5624_2	utf8	355
utf8_5624_3	utf8	356
utf8_5624_4	utf8	357
ucs2_test_ci	ucs2	358
ucs2_vn_ci	ucs2	359
ucs2_5624_1	ucs2	360
utf8_5624_5	utf8	368
utf32_test_ci	utf32	391
utf8_maxuserid_ci	utf8	2047
show collation like '%test%';
Collation	Charset	Id	Default	Compiled	Sortlen	Pad_attribute
latin1_test	latin1	99		Yes	1	PAD SPACE
ucs2_test_ci	ucs2	358			8	PAD SPACE
utf16_test_ci	utf16	327			8	PAD SPACE
utf32_test_ci	utf32	391			8	PAD SPACE
utf8mb4_test_400_ci	utf8mb4	328			8	PAD SPACE
utf8mb4_test_ci	utf8mb4	326			8	PAD SPACE
utf8_test_ci	utf8	353			8	PAD SPACE
show collation like 'ucs2_vn_ci';
Collation	Charset	Id	Default	Compiled	Sortlen	Pad_attribute
ucs2_vn_ci	ucs2	359			8	PAD SPACE
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 hex(c1) from 2byte_collation;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					hex(c1)	253	12	4	Y	0	31	33
hex(c1)
0061
drop table 2byte_collation;
CREATE TABLE 2byte_collation (s1 char(10) character set utf8 collate utf8_maxuserid_ci) engine = innodb;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Warning	3778	'utf8_maxuserid_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
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;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
show collation like 'utf8_maxuserid_ci';
Collation	Charset	Id	Default	Compiled	Sortlen	Pad_attribute
utf8_maxuserid_ci	utf8	2047			8	PAD SPACE
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;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Warning	3778	'utf8_maxuserid_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
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;