set tidb_cost_model_version=1;
create database collation_misc;
use collation_misc;
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
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
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	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
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
ascii_bin	ascii	65	Yes	Yes	1
binary	binary	63	Yes	Yes	1
gb18030_bin	gb18030	249		Yes	1
gb18030_chinese_ci	gb18030	248	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_bin	utf8mb4	46	Yes	Yes	1
utf8mb4_general_ci	utf8mb4	45		Yes	1
utf8mb4_unicode_ci	utf8mb4	224		Yes	1
use test;