forked from pingcap/tidb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcollation_misc_enabled.result
137 lines (134 loc) · 4.27 KB
/
collation_misc_enabled.result
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
set tidb_cost_model_version=1;
create table t1(a varchar(20) charset utf8);
insert into t1 values ("t1_value");
alter table t1 collate uTf8mB4_uNiCoDe_Ci charset Utf8mB4 charset uTF8Mb4 collate UTF8MB4_BiN;
alter table t1 modify column a varchar(20) charset utf8mb4;
select * from t1;
a
t1_value
create table t(a varchar(20) charset latin1);
insert into t values ("t_value");
alter table t modify column a varchar(20) charset latin1;
select * from t;
a
t_value
alter table t modify column a varchar(20) charset utf8;
Error 8200: Unsupported modify charset from latin1 to utf8
drop table t;
create table t(a varchar(20) charset latin1);
insert into t values ("t_value");
alter table t modify column a varchar(20) charset utf8mb4;
admin check table t;
select * from t;
a
t_value
drop table t;
create table t(a varchar(20) charset latin1);
insert into t values ("t_value");
alter table t modify column a varchar(20) charset utf8mb4 collate utf8mb4_general_ci;
admin check table t;
select * from t;
a
t_value
drop table t;
create table t(a varchar(20) charset latin1);
insert into t values ("t_value");
alter table t modify column a varchar(20) charset utf8 collate utf8_bin;
Error 8200: Unsupported modify charset from latin1 to utf8
alter table t modify column a varchar(20) charset utf8mb4 collate utf8bin;
[ddl:1273]Unknown collation: 'utf8bin'
alter table t collate LATIN1_GENERAL_CI charset utf8 collate utf8_bin;
Error 1273: Unsupported collation when new collation is enabled: 'latin1_general_ci'
alter table t collate LATIN1_GENERAL_CI collate UTF8MB4_UNICODE_ci collate utf8_bin;
Error 1273: Unsupported collation when new collation is enabled: 'latin1_general_ci'
drop table t;
create table t(a varchar(20) charset latin1);
insert into t values ("t_value");
alter table t modify column a varchar(19) charset utf8mb4;
admin check table t;
select * from t;
a
t_value
create database if not exists cd_test_utf8 CHARACTER SET utf8 COLLATE utf8_bin;
create database if not exists cd_test_latin1 CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Error 1273: Unsupported collation when new collation is enabled: 'latin1_swedish_ci'
use cd_test_utf8;
select @@character_set_database;
@@character_set_database
utf8
select @@collation_database;
@@collation_database
utf8_bin
use cd_test_latin1;
Error 1049: Unknown database 'cd_test_latin1'
select @@character_set_database;
@@character_set_database
utf8
select @@collation_database;
@@collation_database
utf8_bin
drop database if exists cd_test_utf8;
drop database if exists cd_test_latin1;
create database if not exists test_db CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Error 1273: Unsupported collation when new collation is enabled: 'latin1_swedish_ci'
with cte as (select cast('2010-09-09' as date) a union select '2010-09-09 ') select count(*) from cte;
count(*)
1
set names utf8mb4 collate utf8mb4_general_ci;
select position('a' in 'AA');
position('a' in 'AA')
1
select locate('a', 'AA');
locate('a', 'AA')
1
select locate('a', 'a');
locate('a', 'a')
1
set names utf8mb4;
SELECT default_collate_name, maxlen FROM information_schema.character_sets ORDER BY character_set_name;
default_collate_name maxlen
ascii_bin 1
binary 1
gbk_chinese_ci 2
latin1_bin 1
utf8_bin 3
utf8mb4_bin 4
SELECT character_set_name, id, sortlen FROM information_schema.collations ORDER BY collation_name, id;
character_set_name id sortlen
ascii 65 1
binary 63 1
gbk 87 1
gbk 28 1
latin1 47 1
utf8 83 1
utf8 33 1
utf8 192 1
utf8mb4 255 1
utf8mb4 46 1
utf8mb4 45 1
utf8mb4 224 1
select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY where COLLATION_NAME='utf8mb4_bin';
COLLATION_NAME CHARACTER_SET_NAME
utf8mb4_bin utf8mb4
show charset;
Charset Description Default collation Maxlen
ascii US ASCII ascii_bin 1
binary binary binary 1
gbk Chinese Internal Code Specification gbk_chinese_ci 2
latin1 Latin1 latin1_bin 1
utf8 UTF-8 Unicode utf8_bin 3
utf8mb4 UTF-8 Unicode utf8mb4_bin 4
show collation;
Collation Charset Id Default Compiled Sortlen
ascii_bin ascii 65 Yes Yes 1
binary binary 63 Yes Yes 1
gbk_bin gbk 87 Yes 1
gbk_chinese_ci gbk 28 Yes Yes 1
latin1_bin latin1 47 Yes Yes 1
utf8_bin utf8 83 Yes Yes 1
utf8_general_ci utf8 33 Yes 1
utf8_unicode_ci utf8 192 Yes 1
utf8mb4_0900_ai_ci utf8mb4 255 Yes 1
utf8mb4_bin utf8mb4 46 Yes Yes 1
utf8mb4_general_ci utf8mb4 45 Yes 1
utf8mb4_unicode_ci utf8mb4 224 Yes 1