188 lines
7.5 KiB
Plaintext
188 lines
7.5 KiB
Plaintext
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 (HY000): 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 (HY000): Unsupported modify charset from latin1 to utf8
|
|
alter table t modify column a varchar(20) charset utf8mb4 collate utf8bin;
|
|
Error 1273 (HY000): Unknown collation: 'utf8bin'
|
|
alter table t collate LATIN1_GENERAL_CI charset utf8 collate utf8_bin;
|
|
Got one of the listed errors
|
|
alter table t collate LATIN1_GENERAL_CI collate UTF8MB4_UNICODE_ci collate utf8_bin;
|
|
Got one of the listed errors
|
|
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;
|
|
use cd_test_utf8;
|
|
select @@character_set_database;
|
|
@@character_set_database
|
|
utf8
|
|
select @@collation_database;
|
|
@@collation_database
|
|
utf8_bin
|
|
use cd_test_latin1;
|
|
select @@character_set_database;
|
|
@@character_set_database
|
|
utf8
|
|
select @@collation_database;
|
|
@@collation_database
|
|
utf8_bin
|
|
create database if not exists test_db CHARACTER SET latin1 COLLATE 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
|
|
gb18030_chinese_ci 4
|
|
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
|
|
gb18030 249 1
|
|
gb18030 248 1
|
|
gbk 87 1
|
|
gbk 28 1
|
|
latin1 47 1
|
|
utf8 83 1
|
|
utf8 33 1
|
|
utf8 192 8
|
|
utf8mb4 255 0
|
|
utf8mb4 309 1
|
|
utf8mb4 46 1
|
|
utf8mb4 45 1
|
|
utf8mb4 224 8
|
|
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
|
|
gb18030 China National Standard GB18030 gb18030_chinese_ci 4
|
|
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 Pad_attribute
|
|
ascii_bin ascii 65 Yes Yes 1 PAD SPACE
|
|
binary binary 63 Yes Yes 1 NO PAD
|
|
gb18030_bin gb18030 249 Yes 1 PAD SPACE
|
|
gb18030_chinese_ci gb18030 248 Yes Yes 1 PAD SPACE
|
|
gbk_bin gbk 87 Yes 1 PAD SPACE
|
|
gbk_chinese_ci gbk 28 Yes Yes 1 PAD SPACE
|
|
latin1_bin latin1 47 Yes Yes 1 PAD SPACE
|
|
utf8_bin utf8 83 Yes Yes 1 PAD SPACE
|
|
utf8_general_ci utf8 33 Yes 1 PAD SPACE
|
|
utf8_unicode_ci utf8 192 Yes 8 PAD SPACE
|
|
utf8mb4_0900_ai_ci utf8mb4 255 Yes 0 NO PAD
|
|
utf8mb4_0900_bin utf8mb4 309 Yes 1 NO PAD
|
|
utf8mb4_bin utf8mb4 46 Yes Yes 1 PAD SPACE
|
|
utf8mb4_general_ci utf8mb4 45 Yes 1 PAD SPACE
|
|
utf8mb4_unicode_ci utf8mb4 224 Yes 8 PAD SPACE
|
|
create database if not exists cd_test_utf8mb4_0900_bin;
|
|
use cd_test_utf8mb4_0900_bin;
|
|
create table t (id varchar(255) primary key clustered, a varchar(255) collate utf8mb4_0900_bin, b varchar(255) collate utf8mb4_bin, key idx(a, b));
|
|
insert into t values ("1", "a ", "a");
|
|
select /*+USE_INDEX(t, idx)*/ * from t;
|
|
id a b
|
|
1 a a
|
|
drop table if exists t1;
|
|
drop table if exists t2;
|
|
create table t1(code varchar(32)) CHARSET=utf8 COLLATE=utf8_general_ci;
|
|
create table t2(code varchar(32)) CHARSET=utf8 COLLATE=utf8_bin;
|
|
explain format='plan_tree' select * from t1 join t2 on t1.code=t2.code and t1.code in ('1') and t2.code in ('1');
|
|
id task access object operator info
|
|
Projection root cd_test_utf8mb4_0900_bin.t1.code, cd_test_utf8mb4_0900_bin.t2.code
|
|
└─HashJoin root inner join, equal:[eq(cd_test_utf8mb4_0900_bin.t2.code, cd_test_utf8mb4_0900_bin.t1.code)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] eq(cd_test_utf8mb4_0900_bin.t2.code, "1")
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] eq(cd_test_utf8mb4_0900_bin.t1.code, "1"), not(isnull(cd_test_utf8mb4_0900_bin.t1.code))
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
drop table if exists t1;
|
|
drop table if exists t2;
|
|
create table t1(id_ci varchar(10) charset utf8mb4 collate utf8mb4_general_ci, id_bin varchar(10) charset utf8mb4 collate utf8mb4_bin);
|
|
create table t2(id_ci varchar(10) charset utf8mb4 collate utf8mb4_general_ci, id_bin varchar(10) charset utf8mb4 collate utf8mb4_bin);
|
|
insert into t1 values('a','A');
|
|
insert into t2 values('A','A');
|
|
select * from t1 join t2 on t1.id_ci = t2.id_ci;
|
|
id_ci id_bin id_ci id_bin
|
|
a A A A
|
|
explain format='plan_tree' select * from t1 join t2 on t1.id_ci = t2.id_ci;
|
|
id task access object operator info
|
|
HashJoin root inner join, equal:[eq(cd_test_utf8mb4_0900_bin.t1.id_ci, cd_test_utf8mb4_0900_bin.t2.id_ci)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(cd_test_utf8mb4_0900_bin.t2.id_ci))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] not(isnull(cd_test_utf8mb4_0900_bin.t1.id_ci))
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
select * from t1 join t2 on t1.id_ci = t2.id_ci where t1.id_ci in (t2.id_ci, t2.id_bin);
|
|
id_ci id_bin id_ci id_bin
|
|
explain format='plan_tree' select * from t1 join t2 on t1.id_ci = t2.id_ci where t1.id_ci in (t2.id_ci, t2.id_bin);
|
|
id task access object operator info
|
|
HashJoin root inner join, equal:[eq(cd_test_utf8mb4_0900_bin.t1.id_ci, cd_test_utf8mb4_0900_bin.t2.id_ci)], other cond:in(cd_test_utf8mb4_0900_bin.t1.id_ci, cd_test_utf8mb4_0900_bin.t2.id_ci, cd_test_utf8mb4_0900_bin.t2.id_bin)
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(cd_test_utf8mb4_0900_bin.t2.id_ci))
|
|
│ └─TableFullScan cop[tikv] table:t2 keep order:false, stats:pseudo
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] not(isnull(cd_test_utf8mb4_0900_bin.t1.id_ci))
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|