github.com/pingcap/ticdc@v0.0.0-20220526033649-485a10ef2652/tests/clustered_index/data/test.sql (about)

     1  drop database if exists `clustered_index_test`;
     2  create database `clustered_index_test`;
     3  use `clustered_index_test`;
     4  
     5  CREATE TABLE t0 (
     6  	id VARCHAR(255),
     7  	data INT,
     8  	PRIMARY KEY(id)
     9  );
    10  
    11  INSERT INTO t0 VALUES ('1', 1);
    12  INSERT INTO t0 VALUES ('2', 2);
    13  INSERT INTO t0 VALUES ('3', 3);
    14  INSERT INTO t0 VALUES ('4', 4);
    15  INSERT INTO t0 VALUES ('5', 5);
    16  
    17  DELETE FROM t0 WHERE id = '3';
    18  DELETE FROM t0 WHERE data = 5;
    19  UPDATE t0 SET id = '10' WHERE data = 1;
    20  UPDATE t0 SET data = 555 WHERE id = '10';
    21  
    22  CREATE TABLE t1 (
    23      id VARCHAR(255),
    24      a INT,
    25      b CHAR(10),
    26      PRIMARY KEY(id, b),
    27      UNIQUE KEY(b),
    28      KEY(a)
    29  );
    30  
    31  INSERT INTO t1 VALUES ('111', 111, '111');
    32  INSERT INTO t1 VALUES ('222', 222, '222');
    33  INSERT INTO t1 VALUES ('333', 333, '333');
    34  INSERT INTO t1 VALUES ('444', 444, '444');
    35  INSERT INTO t1 VALUES ('555', 555, '555');
    36  UPDATE t1 SET id = '10' WHERE id = '111';
    37  DELETE FROM t1 WHERE a = 222;
    38  
    39  CREATE TABLE t2 (
    40      id VARCHAR(255),
    41      a INT,
    42      b DECIMAL(5,2),
    43      PRIMARY KEY(id, a),
    44      KEY(id, a),
    45      UNIQUE KEY(id, a)
    46  );
    47  
    48  INSERT INTO t2 VALUES ('aaaa', 1111, 11.0);
    49  INSERT INTO t2 VALUES ('bbbb', 1111, 12.0);
    50  INSERT INTO t2 VALUES ('cccc', 1111, 13.0);
    51  INSERT INTO t2 VALUES ('dddd', 1111, 14.0);
    52  INSERT INTO t2 VALUES ('eeee', 1111, 15.0);
    53  
    54  
    55  create table t_bit(a bit primary key, b int);
    56  INSERT INTO t_bit VALUES(1,2);
    57  INSERT INTO t_bit VALUES(0,3);
    58  
    59  create table t_bool(a bool primary key, b int);
    60  INSERT INTO t_bool VALUES(true,2);
    61  INSERT INTO t_bool VALUES(false,3);
    62  
    63  create table t_tinyint(a tinyint primary key, b int);
    64  INSERT INTO t_tinyint VALUES(6,2);
    65  INSERT INTO t_tinyint VALUES(8,3);
    66  
    67  create table t_smallint(a smallint primary key, b int);
    68  INSERT INTO t_smallint VALUES(432,2);
    69  INSERT INTO t_smallint VALUES(125,3);
    70  
    71  create table t_mediumint(a mediumint primary key, b int);
    72  INSERT INTO t_mediumint VALUES(8567,2);
    73  INSERT INTO t_mediumint VALUES(12341,3);
    74  
    75  create table t_int(a int primary key, b int);
    76  INSERT INTO t_int VALUES(123563,2);
    77  INSERT INTO t_int VALUES(6784356,3);
    78  
    79  create table t_date(a date primary key, b int);
    80  INSERT INTO t_date VALUES ('2020-02-20', 1);
    81  INSERT INTO t_date VALUES ('2020-02-21', 2);
    82  INSERT INTO t_date VALUES ('2020-02-22', 3);
    83  UPDATE t_date SET a = '2020-02-23' WHERE b = 3;
    84  DELETE FROM t_date WHERE b = 2;
    85  
    86  
    87  create table t_time(a time primary key, b int);
    88  
    89  INSERT INTO t_time VALUES ('11:22:33', 1);
    90  INSERT INTO t_time VALUES ('11:33:22', 2);
    91  INSERT INTO t_time VALUES ('11:43:11', 3);
    92  UPDATE t_time SET a = '11:44:55' WHERE b = 3;
    93  DELETE FROM t_time WHERE b = 2;
    94  
    95  create table t_datetime(a datetime primary key, b int);
    96  INSERT INTO t_datetime VALUES ('2020-02-20 11:22:33', 1);
    97  INSERT INTO t_datetime VALUES ('2020-02-21 11:33:22', 2);
    98  INSERT INTO t_datetime VALUES ('2020-02-22 11:43:11', 3);
    99  UPDATE t_datetime SET a = '2020-02-23 11:44:55' WHERE b = 3;
   100  DELETE FROM t_datetime WHERE b = 2;
   101  
   102  create table t_timestamp(a timestamp primary key, b int);
   103  INSERT INTO t_timestamp VALUES ('2020-02-20 11:22:33', 1);
   104  INSERT INTO t_timestamp VALUES ('2020-02-21 11:33:22', 2);
   105  INSERT INTO t_timestamp VALUES ('2020-02-22 11:43:11', 3);
   106  UPDATE t_timestamp SET a = '2020-02-23 11:44:55' WHERE b = 3;
   107  DELETE FROM t_timestamp WHERE b = 2;
   108  
   109  create table t_year(a year primary key, b int);
   110  INSERT INTO t_year VALUES ('2020', 1);
   111  INSERT INTO t_year VALUES ('2021', 2);
   112  INSERT INTO t_year VALUES ('2022', 3);
   113  UPDATE t_year SET a = '2023' WHERE b = 3;
   114  DELETE FROM t_year WHERE b = 2;
   115  
   116  
   117  create table t_char(a char(20) primary key, b int);
   118  INSERT INTO t_char VALUES ('abcc', 1);
   119  INSERT INTO t_char VALUES ('sdff', 2);
   120  UPDATE t_char SET a = 'ppooii' WHERE b = 2;
   121  DELETE FROM t_char WHERE b = 1;
   122  
   123  create table t_varcher(a varchar(255) primary key, b int);
   124  INSERT INTO t_varcher VALUES ('abcc', 1);
   125  INSERT INTO t_varcher VALUES ('sdff', 2);
   126  UPDATE t_varcher SET a = 'ppooii' WHERE b = 2;
   127  DELETE FROM t_varcher WHERE b = 1;
   128  
   129  create table t_text (a text, b int, primary key(a(5)));
   130  INSERT INTO t_text VALUES ('abcc', 1);
   131  INSERT INTO t_text VALUES ('sdff', 2);
   132  UPDATE t_text SET a = 'ppooii' WHERE b = 2;
   133  DELETE FROM t_text WHERE b = 1;
   134  
   135  create table t_binary(a binary(20) primary key, b int);
   136  INSERT INTO t_binary VALUES (x'89504E470D0A1A0A',1),(x'89504E470D0A1A0B',2),(x'89504E470D0A1A0C',3);
   137  update t_binary set a = x'89504E470D0A1A0D' where b = 3;
   138  delete from t_binary where b = 2;
   139  
   140  create table t_blob(a blob, b int, primary key (a(20)));
   141  INSERT INTO t_blob VALUES (x'89504E470D0A1A0A',1),(x'89504E470D0A1A0B',2),(x'89504E470D0A1A0C',3);
   142  update t_blob set a = x'89504E470D0A1A0D' where b = 3;
   143  delete from t_binary where b = 2;
   144  
   145  create table t_enum(e enum('a', 'b', 'c') primary key, b int);
   146  INSERT INTO t_enum VALUES ('a',1),('b',2),('c',3);
   147  delete from t_enum where b = 2;
   148  update t_enum set e = 'b' where b = 3;
   149  
   150  create table t_set(s set('a', 'b', 'c') primary key, b int);
   151  INSERT INTO t_set VALUES ('a',1),('b,c',2),('a,c',3);
   152  delete from t_set where b = 2;
   153  update t_set set s = 'b' where b = 3;
   154  
   155  
   156  create table t8(a int, b varchar(255) as (concat(a, "test")) stored, primary key(b));
   157  INSERT INTO t8(a) VALUES (2020);
   158  INSERT INTO t8(a) VALUES (2021);
   159  INSERT INTO t8(a) VALUES (2022);
   160  UPDATE t8 SET a = 2023 WHERE a = 2022;
   161  DELETE FROM t8 WHERE a = 2021;
   162  
   163  
   164  create table t9(a int, b varchar(255), c int, primary key(a ,b));
   165  insert into t9 values(1, "aaa", 1),(2, "bbb", 2),(3, "ccc", 3);
   166  update t9 set b='ddd' where c = 3;
   167  delete from t9 where c = 2;
   168  
   169  create table t10(a int, b int, c int, primary key(a, b));
   170  insert into t10 values(1, 1, 1),(2, 2, 2),(3, 3, 3);
   171  update t10 set b = 4 where a = 3;
   172  delete from t10 where a = 2;
   173  
   174  create table t11(a int, b float, c int, primary key(a,b));
   175  insert into t11 values(1, 1.1, 1),(2, 2.2, 2),(3, 3.3, 3);
   176  update t11 set b = 4.4 where c = 3;
   177  delete from t11 where b = 2;
   178  
   179    
   180  create table t12(name char(255) primary key, b int, c int, index idx(name), unique index uidx(name));
   181  insert into t12 values("aaaa", 1, 1), ("bbb", 2, 2), ("ccc", 3, 3);
   182  update t12 set name = 'ddd' where c = 3;
   183  delete from t12 where c = 2;