github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/tests/integration_tests/new_ci_collation/data/test1.sql (about)

     1  drop database if exists `new_ci_collation_test`;
     2  create database `new_ci_collation_test`;
     3  use `new_ci_collation_test`;
     4  
     5  CREATE TABLE t1 (
     6      a varchar(20) charset utf8mb4 collate utf8mb4_general_ci primary key,
     7      b int default 10
     8  );
     9  
    10  CREATE TABLE t2 (
    11      a varchar(10) charset utf8 collate utf8_general_ci, primary key(a),
    12      b int default 10
    13  );
    14  
    15  CREATE TABLE t3 (
    16      id int primary key auto_increment,
    17      a varchar(20) charset utf8mb4 collate utf8mb4_general_ci,
    18      b int default 10
    19  );
    20  
    21  CREATE TABLE t4 (
    22      a int primary key,
    23      b varchar(10) charset utf8mb4 collate utf8mb4_general_ci,
    24      c varchar(10) charset utf8 collate utf8_general_ci,
    25      d varchar(10) not null,
    26      unique key d(d)
    27  );
    28  
    29  CREATE TABLE t5 (
    30      a varchar(10) charset utf8mb4 collate utf8mb4_bin, primary key(a),
    31      b int default 10
    32  );
    33  
    34  insert into t1 (a) values ('A'),(' A'),('A\t'),('b'),('bA'),('bac'),('ab');
    35  insert into t1 (a) values ('😉');
    36  insert into t2 (a) values ('A'),(' A'),('A\t'),('b'),('bA'),('bac'),('ab');
    37  insert into t3 (a) values ('A'),('A '),('A   '),(' A'),('A\t'),('A\t ');
    38  insert into t3 (a) values ('a'),('a '),('a   '),(' a'),('a\t'),('a\t ');
    39  insert into t3 (a) values ('B'),('B '),('B   '),(' B'),('B\t'),('B\t ');
    40  insert into t3 (a) values ('b'),('b '),('b   '),(' b'),('b\t'),('b\t ');
    41  insert into t4 values (1,'A','A','1'),(2,'a\t','a\t','2'),(3,'ab','ab','3'),(4,'abc','abc','4');
    42  insert into t5 (a) values ('😉');
    43  insert into t5 (a) values ('a'),('A'),(' a'),(' A'),('a\t'),('ab'),('Ab');
    44  update t1 set b = b + 1;
    45  update t2 set b = 13;
    46  update t3 set b = 11 where a > 'A';
    47  drop index `primary` on t4;
    48  update t5 set b = 12;
    49  
    50  
    51  CREATE TABLE tt1 (
    52                      a varchar(20) charset utf8mb4 collate utf8mb4_0900_ai_ci primary key,
    53                      b int default 10
    54  );
    55  
    56  CREATE TABLE tt2 (
    57                       a varchar(20) charset utf8mb4 collate utf8mb4_0900_ai_ci,
    58                       b int default 10, primary key(b)
    59  );
    60  
    61  CREATE TABLE tt3 (
    62                      id int primary key auto_increment,
    63                      a varchar(20) charset utf8mb4 collate utf8mb4_0900_ai_ci,
    64                      b int default 10
    65  );
    66  
    67  CREATE TABLE tt4 (
    68                      a int primary key,
    69                      b varchar(10) charset utf8mb4 collate utf8mb4_0900_ai_ci,
    70                      c varchar(10) charset utf8mb4 collate utf8mb4_0900_ai_ci,
    71                      d varchar(10) not null,
    72                      unique key d(d)
    73  );
    74  
    75  insert into tt1 (a) values ('A'),(' A'),('A\t'),('b'),('bA'),('bac'),('ab'),('\U000FFFFE'),('Àoo'),('ß'),('æ'),('aeoo'), ('𝕒bc');
    76  insert into tt1 (a) values ('😉');
    77  insert into tt2 (a, b) values ('A', 1),(' A', 2),('A\t', 3),('b', 4),('bA', 5),('bac', 6),('ab', 7),('\U000FFFFE', 8),('À', 9),('ß', 10),('æ', 11),('ae', 12), ('𝕒bc', 13);
    78  insert into tt2 (a, b) values ('😉', 14);
    79  insert into tt3 (a) values ('A'),('A '),('A   '),(' A'),('A\t'),('A\t '),('\U000FFFFE'),('À'),('ß'),('æ'),('ae'), ('𝕒bc');
    80  insert into tt3 (a) values ('a'),('a '),('a   '),(' a'),('a\t'),('a\t '),('\U000FFFFE'),('À'),('ß'),('æ'),('ae'), ('𝕒bc');
    81  insert into tt3 (a) values ('B'),('B '),('B   '),(' B'),('B\t'),('B\t '),('\U000FFFFE'),('À'),('ß'),('æ'),('ae'), ('𝕒bc');
    82  insert into tt3 (a) values ('b'),('b '),('b   '),(' b'),('b\t'),('b\t '),('\U000FFFFE'),('À'),('ß'),('æ'),('ae'), ('');
    83  insert into tt4 values (1,'A','A','1'),(2,'a\t','a\t','2'),(3,'ab','ab','3'),(4,'abc','abc','4'),(5,'À','À','5'),(6,'ß','ß','6'),(7,'æ','æ','7'),(8,'ae','ae','8'),(9,'𝕒bc','𝕒bc','9');
    84  update tt1 set b = b + 1;
    85  update tt3 set b = 11 where a > 'A';
    86  update tt3 set b = '14' where a > 'À';
    87  drop index `primary` on tt4;