1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
|
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_nb_0900_ai_ci utf8mb4 310
utf8mb4_nb_0900_as_cs utf8mb4 311
utf8mb4_nn_0900_ai_ci utf8mb4 312
utf8mb4_nn_0900_as_cs utf8mb4 313
utf8mb4_sr_latn_0900_ai_ci utf8mb4 314
utf8mb4_sr_latn_0900_as_cs utf8mb4 315
utf8mb4_bs_0900_ai_ci utf8mb4 316
utf8mb4_bs_0900_as_cs utf8mb4 317
utf8mb4_bg_0900_ai_ci utf8mb4 318
utf8mb4_bg_0900_as_cs utf8mb4 319
utf8mb4_gl_0900_ai_ci utf8mb4 320
utf8mb4_gl_0900_as_cs utf8mb4 321
utf8mb4_mn_cyrl_0900_ai_ci utf8mb4 322
utf8mb4_mn_cyrl_0900_as_cs utf8mb4 323
utf8mb4_test_ci utf8mb4 326
utf16_test_ci utf16 327
utf8mb4_test_400_ci utf8mb4 328
utf8_bengali_standard_ci utf8mb3 336
utf8_bengali_traditional_ci utf8mb3 337
utf8_phone_ci utf8mb3 352
utf8_test_ci utf8mb3 353
utf8_5624_1 utf8mb3 354
utf8_5624_2 utf8mb3 355
utf8_5624_3 utf8mb3 356
utf8_5624_4 utf8mb3 357
ucs2_test_ci ucs2 358
ucs2_vn_ci ucs2 359
ucs2_5624_1 ucs2 360
utf8_5624_5 utf8mb3 368
utf32_test_ci utf32 391
latin1_test latin1 1025
utf8_maxuserid_ci utf8mb3 2047
show collation like '%test%';
Collation Charset Id Default Compiled Sortlen Pad_attribute
latin1_test latin1 1025 0 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 utf8mb3 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;
Warnings:
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
Warning 4079 'ucs2_vn_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
Warning 4162 'ucs2_vn_ci' is a user defined collation. User defined collations are deprecated and will be removed in a future release. Consider using a compiled collation instead.
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.
Warning 4162 'utf8_maxuserid_ci' is a user defined collation. User defined collations are deprecated and will be removed in a future release. Consider using a compiled collation instead.
INSERT INTO 2byte_collation VALUES ('a'),('b');
SELECT * FROM 2byte_collation WHERE s1='a' ORDER BY BINARY s1;
s1
a
b
Warnings:
Warning 1287 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
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 utf8mb3 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.
Warning 4162 'utf8_maxuserid_ci' is a user defined collation. User defined collations are deprecated and will be removed in a future release. Consider using a compiled 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;
|
