drop table if exists t1;
In the following tests we change the order of letter "b"
making it equal to letter "a", and check that it works
with all Unicode character sets
set names utf8;
show variables like 'character_sets_dir%';
Variable_name Value
character_sets_dir MYSQL_TEST_DIR/std_data/ldml/
show collation like 'utf8mb3_phone_ci';
Collation Charset Id Default Compiled Sortlen
utf8mb3_phone_ci utf8mb3 352 8
CREATE TABLE t1 (
name VARCHAR(64),
phone VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_phone_ci
);
INSERT INTO t1 VALUES ('Svoj','+7 912 800 80 02');
INSERT INTO t1 VALUES ('Hf','+7 (912) 800 80 04');
INSERT INTO t1 VALUES ('Bar','+7-912-800-80-01');
INSERT INTO t1 VALUES ('Ramil','(7912) 800 80 03');
INSERT INTO t1 VALUES ('Sanja','+380 (912) 8008005');
SELECT * FROM t1 ORDER BY phone;
name phone
Sanja +380 (912) 8008005
Bar +7-912-800-80-01
Svoj +7 912 800 80 02
Ramil (7912) 800 80 03
Hf +7 (912) 800 80 04
SELECT * FROM t1 WHERE phone='+7(912)800-80-01';
name phone
Bar +7-912-800-80-01
SELECT * FROM t1 WHERE phone='79128008001';
name phone
Bar +7-912-800-80-01
SELECT * FROM t1 WHERE phone='7 9 1 2 8 0 0 8 0 0 1';
name phone
Bar +7-912-800-80-01
SELECT * FROM t1 WHERE phone='tel.79128008001';
name phone
Bar +7-912-800-80-01
DROP TABLE t1;
show collation like 'utf8mb3_test_ci';
Collation Charset Id Default Compiled Sortlen
utf8mb3_test_ci utf8mb3 353 8
create table t1 (c1 char(1) character set utf8 collate utf8_test_ci);
insert into t1 values ('a');
select * from t1 where c1='b';
c1
a
drop table t1;
show collation like 'ucs2_test_ci';
Collation Charset Id Default Compiled Sortlen
ucs2_test_ci ucs2 358 8
create table t1 (c1 char(1) character set ucs2 collate ucs2_test_ci);
insert into t1 values ('a');
select * from t1 where c1='b';
c1
a
drop table t1;
show collation like 'utf8mb4_test_ci';
Collation Charset Id Default Compiled Sortlen
utf8mb4_test_ci utf8mb4 326 8
create table t1 (c1 char(1) character set utf8mb4 collate utf8mb4_test_ci);
insert into t1 values ('a');
select * from t1 where c1='b';
c1
a
drop table t1;
show collation like 'utf16_test_ci';
Collation Charset Id Default Compiled Sortlen
utf16_test_ci utf16 327 8
create table t1 (c1 char(1) character set utf16 collate utf16_test_ci);
insert into t1 values ('a');
select * from t1 where c1='b';
c1
a
drop table t1;
show collation like 'utf32_test_ci';
Collation Charset Id Default Compiled Sortlen
utf32_test_ci utf32 391 8
create table t1 (c1 char(1) character set utf32 collate utf32_test_ci);
insert into t1 values ('a');
select * from t1 where c1='b';
c1
a
drop table t1;
SELECT hex(weight_string(_utf8mb4'a' collate utf8mb4_test_ci));
hex(weight_string(_utf8mb4'a' collate utf8mb4_test_ci))
120F
SELECT hex(weight_string(convert(_utf32 0x10002 using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_utf32 0x10002 using utf8mb4) collate utf8mb4_test_ci))
314A
SELECT hex(@a:=convert(_utf32 0x10400 using utf8mb4) collate utf8mb4_test_ci), hex(lower(@a));
hex(@a:=convert(_utf32 0x10400 using utf8mb4) collate utf8mb4_test_ci) hex(lower(@a))
F0909080 F09090A8
SELECT hex(@a:=convert(_utf32 0x10428 using utf8mb4) collate utf8mb4_test_ci), hex(upper(@a));
hex(@a:=convert(_utf32 0x10428 using utf8mb4) collate utf8mb4_test_ci) hex(upper(@a))
F09090A8 F0909080
SELECT hex(@a:=convert(_utf32 0x2C00 using utf8mb4) collate utf8mb4_test_ci), hex(lower(@a));
hex(@a:=convert(_utf32 0x2C00 using utf8mb4) collate utf8mb4_test_ci) hex(lower(@a))
E2B080 E2B0B0
SELECT hex(@a:=convert(_utf32 0x2C30 using utf8mb4) collate utf8mb4_test_ci), hex(upper(@a));
hex(@a:=convert(_utf32 0x2C30 using utf8mb4) collate utf8mb4_test_ci) hex(upper(@a))
E2B0B0 E2B080
SELECT hex(weight_string(convert(_utf32 0x61 using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_utf32 0x61 using utf8mb4) collate utf8mb4_test_ci))
120F
SELECT hex(weight_string(convert(_utf32 0x62 using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_utf32 0x62 using utf8mb4) collate utf8mb4_test_ci))
120F
SELECT hex(weight_string(convert(_utf32 0x10062 using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_utf32 0x10062 using utf8mb4) collate utf8mb4_test_ci))
120F
SELECT hex(weight_string(convert(_utf32 0x10400 using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_utf32 0x10400 using utf8mb4) collate utf8mb4_test_ci))
30D2
SELECT hex(weight_string(convert(_utf32 0x100400 using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_utf32 0x100400 using utf8mb4) collate utf8mb4_test_ci))
30D2
SELECT hex(weight_string(_utf8mb4 0x64 collate utf8mb4_test_ci));
hex(weight_string(_utf8mb4 0x64 collate utf8mb4_test_ci))
1250
SELECT hex(weight_string(convert(_ucs2 0x0064017e using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_ucs2 0x0064017e using utf8mb4) collate utf8mb4_test_ci))
1251
SELECT hex(weight_string(convert(_ucs2 0x0044017e using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_ucs2 0x0044017e using utf8mb4) collate utf8mb4_test_ci))
1251
SELECT hex(weight_string(convert(_ucs2 0x0044017d using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_ucs2 0x0044017d using utf8mb4) collate utf8mb4_test_ci))
1251
CREATE TABLE t1 (
col1 varchar(100) character set utf8 collate utf8_test_ci
);
INSERT INTO t1 (col1) VALUES ('abcd'),('efgh'),('ijkl');
ALTER TABLE t1 ADD FULLTEXT INDEX (col1);
SELECT * FROM t1 where match (col1) against ('abcd');
col1
abcd
SELECT * FROM t1 where match (col1) against ('abcd' IN BOOLEAN MODE);
col1
abcd
ALTER TABLE t1 ADD (col2 varchar(100) character set latin1);
UPDATE t1 SET col2=col1;
SELECT * FROM t1 WHERE col1=col2 ORDER BY col1;
col1 col2
abcd abcd
efgh efgh
ijkl ijkl
DROP TABLE t1;
#
# Bug#45645 Mysql server close all connection and restart using lower function
#
CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8 COLLATE utf8_test_ci;
INSERT INTO t1 (a) VALUES ('hello!');
SELECT * FROM t1 WHERE LOWER(a)=LOWER('N');
a
DROP TABLE t1;
#
# Bug#51976 LDML collations issue (cyrillic example)
#
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci);
INSERT INTO t1 (a) VALUES ('Hello');
SELECT a, UPPER(a), LOWER(a) FROM t1;
a UPPER(a) LOWER(a)
Hello HELLO hello
DROP TABLE t1;
#
# Bug#43827 Server closes connections and restarts
#
CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci);
INSERT IGNORE INTO t1 SELECT REPEAT('a',11);
Warnings:
Warning 1265 Data truncated for column 'c1' at row 1
DROP TABLE t1;
Vietnamese experimental collation
show collation like 'ucs2_vn_ci';
Collation Charset Id Default Compiled Sortlen
ucs2_vn_ci ucs2 359 8
create table t1 (c1 char(1) character set ucs2 collate ucs2_vn_ci);
insert into t1 values (0x0061),(0x0041),(0x00E0),(0x00C0),(0x1EA3),(0x1EA2),
(0x00E3),(0x00C3),(0x00E1),(0x00C1),(0x1EA1),(0x1EA0);
insert into t1 values (0x0103),(0x0102),(0x1EB1),(0x1EB0),(0x1EB3),(0x1EB2),
(0x1EB5),(0x1EB4),(0x1EAF),(0x1EAE),(0x1EB7),(0x1EB6);
insert into t1 values (0x00E2),(0x00C2),(0x1EA7),(0x1EA6),(0x1EA9),(0x1EA8),
(0x1EAB),(0x1EAA),(0x1EA5),(0x1EA4),(0x1EAD),(0x1EAC);
insert into t1 values ('b'),('B'),('c'),('C');
insert into t1 values ('d'),('D'),(0x0111),(0x0110);
insert into t1 values (0x0065),(0x0045),(0x00E8),(0x00C8),(0x1EBB),(0x1EBA),
(0x1EBD),(0x1EBC),(0x00E9),(0x00C9),(0x1EB9),(0x1EB8);
insert into t1 values (0x00EA),(0x00CA),(0x1EC1),(0x1EC0),(0x1EC3),(0x1EC2),
(0x1EC5),(0x1EC4),(0x1EBF),(0x1EBE),(0x1EC7),(0x1EC6);
insert into t1 values ('g'),('G'),('h'),('H');
insert into t1 values (0x0069),(0x0049),(0x00EC),(0x00CC),(0x1EC9),(0x1EC8),
(0x0129),(0x0128),(0x00ED),(0x00CD),(0x1ECB),(0x1ECA);
insert into t1 values ('k'),('K'),('l'),('L'),('m'),('M');
insert into t1 values (0x006F),(0x004F),(0x00F2),(0x00D2),(0x1ECF),(0x1ECE),
(0x00F5),(0x00D5),(0x00F3),(0x00D3),(0x1ECD),(0x1ECC);
insert into t1 values (0x00F4),(0x00D4),(0x1ED3),(0x1ED2),(0x1ED5),(0x1ED4),
(0x1ED7),(0x1ED6),(0x1ED1),(0x1ED0),(0x1ED9),(0x1ED8);
insert into t1 values (0x01A1),(0x01A0),(0x1EDD),(0x1EDC),(0x1EDF),(0x1EDE),
(0x1EE1),(0x1EE0),(0x1EDB),(0x1EDA),(0x1EE3),(0x1EE2);
insert into t1 values ('p'),('P'),('q'),('Q'),('r'),('R'),('s'),('S'),('t'),('T');
insert into t1 values (0x0075),(0x0055),(0x00F9),(0x00D9),(0x1EE7),(0x1EE6),
(0x0169),(0x0168),(0x00FA),(0x00DA),(0x1EE5),(0x1EE4);
insert into t1 values (0x01B0),(0x01AF),(0x1EEB),(0x1EEA),(0x1EED),(0x1EEC),
(0x1EEF),(0x1EEE),(0x1EE9),(0x1EE8),(0x1EF1),(0x1EF0);
insert into t1 values ('v'),('V'),('x'),('X');
insert into t1 values (0x0079),(0x0059),(0x1EF3),(0x1EF2),(0x1EF7),(0x1EF6),
(0x1EF9),(0x1EF8),(0x00FD),(0x00DD),(0x1EF5),(0x1EF4);
select hex(c1) as h, c1 from t1 order by c1, h;
h c1
0041 A
0061 a
00C0 À
00C1 Á
00C3 Ã
00E0 à
00E1 á
00E3 ã
1EA0 Ạ
1EA1 ạ
1EA2 Ả
1EA3 ả
0102 Ă
0103 ă
1EAE Ắ
1EAF ắ
1EB0 Ằ
1EB1 ằ
1EB2 Ẳ
1EB3 ẳ
1EB4 Ẵ
1EB5 ẵ
1EB6 Ặ
1EB7 ặ
00C2 Â
00E2 â
1EA4 Ấ
1EA5 ấ
1EA6 Ầ
1EA7 ầ
1EA8 Ẩ
1EA9 ẩ
1EAA Ẫ
1EAB ẫ
1EAC Ậ
1EAD ậ
0042 B
0062 b
0043 C
0063 c
0044 D
0064 d
0110 Đ
0111 đ
0045 E
0065 e
00C8 È
00C9 É
00E8 è
00E9 é
1EB8 Ẹ
1EB9 ẹ
1EBA Ẻ
1EBB ẻ
1EBC Ẽ
1EBD ẽ
00CA Ê
00EA ê
1EBE Ế
1EBF ế
1EC0 Ề
1EC1 ề
1EC2 Ể
1EC3 ể
1EC4 Ễ
1EC5 ễ
1EC6 Ệ
1EC7 ệ
0047 G
0067 g
0048 H
0068 h
0049 I
0069 i
00CC Ì
00CD Í
00EC ì
00ED í
0128 Ĩ
0129 ĩ
1EC8 Ỉ
1EC9 ỉ
1ECA Ị
1ECB ị
004B K
006B k
004C L
006C l
004D M
006D m
004F O
006F o
00D2 Ò
00D3 Ó
00D5 Õ
00F2 ò
00F3 ó
00F5 õ
1ECC Ọ
1ECD ọ
1ECE Ỏ
1ECF ỏ
00D4 Ô
00F4 ô
1ED0 Ố
1ED1 ố
1ED2 Ồ
1ED3 ồ
1ED4 Ổ
1ED5 ổ
1ED6 Ỗ
1ED7 ỗ
1ED8 Ộ
1ED9 ộ
01A0 Ơ
01A1 ơ
1EDA Ớ
1EDB ớ
1EDC Ờ
1EDD ờ
1EDE Ở
1EDF ở
1EE0 Ỡ
1EE1 ỡ
1EE2 Ợ
1EE3 ợ
0050 P
0070 p
0051 Q
0071 q
0052 R
0072 r
0053 S
0073 s
0054 T
0074 t
0055 U
0075 u
00D9 Ù
00DA Ú
00F9 ù
00FA ú
0168 Ũ
0169 ũ
1EE4 Ụ
1EE5 ụ
1EE6 Ủ
1EE7 ủ
01AF Ư
01B0 ư
1EE8 Ứ
1EE9 ứ
1EEA Ừ
1EEB ừ
1EEC Ử
1EED ử
1EEE Ữ
1EEF ữ
1EF0 Ự
1EF1 ự
0056 V
0076 v
0058 X
0078 x
0059 Y
0079 y
00DD Ý
00FD ý
1EF2 Ỳ
1EF3 ỳ
1EF4 Ỵ
1EF5 ỵ
1EF6 Ỷ
1EF7 ỷ
1EF8 Ỹ
1EF9 ỹ
select group_concat(hex(c1) order by hex(c1)) from t1 group by c1;
group_concat(hex(c1) order by hex(c1))
0041,0061,00C0,00C1,00C3,00E0,00E1,00E3,1EA0,1EA1,1EA2,1EA3
0102,0103,1EAE,1EAF,1EB0,1EB1,1EB2,1EB3,1EB4,1EB5,1EB6,1EB7
00C2,00E2,1EA4,1EA5,1EA6,1EA7,1EA8,1EA9,1EAA,1EAB,1EAC,1EAD
0042,0062
0043,0063
0044,0064
0110,0111
0045,0065,00C8,00C9,00E8,00E9,1EB8,1EB9,1EBA,1EBB,1EBC,1EBD
00CA,00EA,1EBE,1EBF,1EC0,1EC1,1EC2,1EC3,1EC4,1EC5,1EC6,1EC7
0047,0067
0048,0068
0049,0069,00CC,00CD,00EC,00ED,0128,0129,1EC8,1EC9,1ECA,1ECB
004B,006B
004C,006C
004D,006D
004F,006F,00D2,00D3,00D5,00F2,00F3,00F5,1ECC,1ECD,1ECE,1ECF
00D4,00F4,1ED0,1ED1,1ED2,1ED3,1ED4,1ED5,1ED6,1ED7,1ED8,1ED9
01A0,01A1,1EDA,1EDB,1EDC,1EDD,1EDE,1EDF,1EE0,1EE1,1EE2,1EE3
0050,0070
0051,0071
0052,0072
0053,0073
0054,0074
0055,0075,00D9,00DA,00F9,00FA,0168,0169,1EE4,1EE5,1EE6,1EE7
01AF,01B0,1EE8,1EE9,1EEA,1EEB,1EEC,1EED,1EEE,1EEF,1EF0,1EF1
0056,0076
0058,0078
0059,0079,00DD,00FD,1EF2,1EF3,1EF4,1EF5,1EF6,1EF7,1EF8,1EF9
select group_concat(c1 order by hex(c1) SEPARATOR '') from t1 group by c1;
group_concat(c1 order by hex(c1) SEPARATOR '')
AaÀÁÃàáãẠạẢả
ĂăẮắẰằẲẳẴẵẶặ
ÂâẤấẦầẨẩẪẫẬậ
Bb
Cc
Dd
Đđ
EeÈÉèéẸẹẺẻẼẽ
ÊêẾếỀềỂểỄễỆệ
Gg
Hh
IiÌÍìíĨĩỈỉỊị
Kk
Ll
Mm
OoÒÓÕòóõỌọỎỏ
ÔôỐốỒồỔổỖỗỘộ
ƠơỚớỜờỞởỠỡỢợ
Pp
Qq
Rr
Ss
Tt
UuÙÚùúŨũỤụỦủ
ƯưỨứỪừỬửỮữỰự
Vv
Xx
YyÝýỲỳỴỵỶỷỸỹ
drop table t1;
Bug#46448 trailing spaces are not ignored when user collation maps space != 0x20
set names latin1;
show collation like 'latin1_test';
Collation Charset Id Default Compiled Sortlen
latin1_test latin1 331 1
select "foo" = "foo " collate latin1_test;
"foo" = "foo " collate latin1_test
1
The following tests check that two-byte collation IDs work
select * from information_schema.collations where id>256 and is_compiled<>'Yes' order by id;
COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN
ascii2_general_nopad_ci ascii2 318 1
ascii2_bin2 ascii2 319 1
ascii2_general_ci ascii2 320 Yes 1
ascii2_bin ascii2 321 1
ascii2_general_inherited_ci ascii2 322 1
ascii2_general_inherited2_ci ascii2 323 1
ascii2_badly_inherited_ci ascii2 324 1
ascii2_nopad_bin ascii2 325 1
utf8mb4_test_ci utf8mb4 326 8
utf16_test_ci utf16 327 8
utf8mb4_test_400_ci utf8mb4 328 8
utf8mb4_test_520_nopad_ci utf8mb4 329 8
utf8mb4_uca1400_test01_as_ci utf8mb4 330 4
latin1_test latin1 331 1
latin1_test2 latin1 332 1
latin1_test2_cs latin1 333 1
latin1_swedish_nopad2_ci latin1 334 1
utf8mb3_bengali_standard_ci utf8mb3 336 8
utf8mb3_bengali_traditional_ci utf8mb3 337 8
utf8mb3_implicit_weights_ci utf8mb3 338 8
utf8mb3_phone_ci utf8mb3 352 8
utf8mb3_test_ci utf8mb3 353 8
utf8mb3_5624_1 utf8mb3 354 8
utf8mb3_5624_2 utf8mb3 355 8
utf8mb3_5624_3 utf8mb3 356 8
utf8mb3_5624_4 utf8mb3 357 8
ucs2_test_ci ucs2 358 8
ucs2_vn_ci ucs2 359 8
ucs2_5624_1 ucs2 360 8
utf8mb3_5624_5 utf8mb3 368 8
utf8mb3_5624_5_bad utf8mb3 369 8
utf8mb3_czech_test_w2 utf8mb3 370 4
utf8mb3_czech_test_nopad_w2 utf8mb3 371 4
utf8mb3_czech_test_bad_w2 utf8mb3 372 4
utf32_test_ci utf32 391 8
utf8mb3_maxuserid_ci utf8mb3 2047 8
show collation like '%test%';
Collation Charset Id Default Compiled Sortlen
latin1_test latin1 331 1
latin1_test2 latin1 332 1
latin1_test2_cs latin1 333 1
utf8mb3_test_ci utf8mb3 353 8
utf8mb3_czech_test_w2 utf8mb3 370 4
utf8mb3_czech_test_nopad_w2 utf8mb3 371 4
utf8mb3_czech_test_bad_w2 utf8mb3 372 4
ucs2_test_ci ucs2 358 8
utf8mb4_test_ci utf8mb4 326 8
utf8mb4_test_400_ci utf8mb4 328 8
utf8mb4_test_520_nopad_ci utf8mb4 329 8
utf8mb4_uca1400_test01_as_ci utf8mb4 330 4
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 t1 (c1 char(1) character set ucs2 collate ucs2_vn_ci);
insert into t1 values (0x0061);
set @@character_set_results=NULL;
select * from t1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def test t1 t1 c1 c1 254 2 2 Y 0 0 359
c1
|