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

     1  drop database if exists `common_1`;
     2  create database `common_1`;
     3  use `common_1`;
     4  
     5  -- multi data type test
     6  
     7  CREATE TABLE cdc_multi_data_type
     8  (
     9      id          INT AUTO_INCREMENT,
    10      t_boolean   BOOLEAN,
    11      t_bigint    BIGINT,
    12      t_double    DOUBLE,
    13      t_decimal   DECIMAL(38, 19),
    14      t_bit       BIT(64),
    15      t_date      DATE,
    16      t_datetime  DATETIME,
    17      t_timestamp TIMESTAMP NULL,
    18      t_time      TIME,
    19      t_year      YEAR,
    20      t_char      CHAR,
    21      t_varchar   VARCHAR(10),
    22      t_blob      BLOB,
    23      t_text      TEXT,
    24      t_enum      ENUM ('enum1', 'enum2', 'enum3'),
    25      t_set       SET ('a', 'b', 'c'),
    26      t_json      JSON,
    27      PRIMARY KEY (id)
    28  ) ENGINE = InnoDB
    29    DEFAULT CHARSET = utf8
    30    COLLATE = utf8_bin;
    31  
    32  INSERT INTO cdc_multi_data_type( t_boolean, t_bigint, t_double, t_decimal, t_bit
    33                                 , t_date, t_datetime, t_timestamp, t_time, t_year
    34                                 , t_char, t_varchar, t_blob, t_text, t_enum
    35                                 , t_set, t_json)
    36  VALUES ( true, 9223372036854775807, 123.123, 123456789012.123456789012, b'1000001'
    37         , '1000-01-01', '9999-12-31 23:59:59', '19731230153000', '23:59:59', 1970
    38         , '测', '测试', 'blob', '测试text', 'enum2'
    39         , 'a,b', NULL);
    40  
    41  INSERT INTO cdc_multi_data_type( t_boolean, t_bigint, t_double, t_decimal, t_bit
    42                                 , t_date, t_datetime, t_timestamp, t_time, t_year
    43                                 , t_char, t_varchar, t_blob, t_text, t_enum
    44                                 , t_set, t_json)
    45  VALUES ( true, 9223372036854775807, 678, 321, b'1000001'
    46         , '1000-01-01', '9999-12-31 23:59:59', '19731230153000', '23:59:59', 1970
    47         , '测', '测试', 'blob', '测试text', 'enum2'
    48         , 'a,b', NULL);
    49  
    50  INSERT INTO cdc_multi_data_type(t_boolean)
    51  VALUES (TRUE);
    52  
    53  INSERT INTO cdc_multi_data_type(t_boolean)
    54  VALUES (FALSE);
    55  
    56  INSERT INTO cdc_multi_data_type(t_bigint)
    57  VALUES (-9223372036854775808);
    58  
    59  INSERT INTO cdc_multi_data_type(t_bigint)
    60  VALUES (9223372036854775807);
    61  
    62  INSERT INTO cdc_multi_data_type(t_json)
    63  VALUES ('{
    64    "key1": "value1",
    65    "key2": "value2"
    66  }');
    67  
    68  -- view test
    69  
    70  CREATE TABLE t1
    71  (
    72      id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    73      c1 INT NOT NULL
    74  );
    75  
    76  INSERT INTO t1 (c1)
    77  VALUES (1),
    78         (2),
    79         (3),
    80         (4),
    81         (5);
    82  
    83  CREATE VIEW v1 AS
    84  SELECT *
    85  FROM t1
    86  WHERE c1 > 2;
    87  
    88  -- uk without pk
    89  -- https://internal.pingcap.net/jira/browse/TOOL-714
    90  -- CDC don't support UK is null
    91  
    92  CREATE TABLE uk_without_pk
    93  (
    94      id INT,
    95      a1 INT NOT NULL,
    96      a3 INT NOT NULL,
    97      UNIQUE KEY dex1 (a1, a3)
    98  );
    99  
   100  INSERT INTO uk_without_pk(id, a1, a3)
   101  VALUES (1, 1, 2);
   102  
   103  INSERT INTO uk_without_pk(id, a1, a3)
   104  VALUES (2, 1, 1);
   105  
   106  UPDATE uk_without_pk
   107  SET id = 10,
   108      a1 = 2
   109  WHERE a1 = 1;
   110  
   111  UPDATE uk_without_pk
   112  SET id = 100
   113  WHERE a1 = 10;
   114  
   115  UPDATE uk_without_pk
   116  SET a3 = 4
   117  WHERE a3 = 1;
   118  
   119  -- bit column
   120  -- Test issue: TOOL-1346
   121  
   122  CREATE TABLE binlog_insert_bit
   123  (
   124      a BIT(1) PRIMARY KEY,
   125      b BIT(64)
   126  );
   127  
   128  INSERT INTO binlog_insert_bit
   129  VALUES (0x01, 0xffffffff);
   130  
   131  UPDATE binlog_insert_bit
   132  SET a = 0x00,
   133      b = 0xfffffffe;
   134  
   135  -- recover test
   136  -- Test issue: TOOL-1407
   137  CREATE TABLE recover_and_insert
   138  (
   139      id INT PRIMARY KEY,
   140      a  INT
   141  );
   142  
   143  INSERT INTO recover_and_insert(id, a)
   144  VALUES (1, -1);
   145  
   146  UPDATE recover_and_insert
   147  SET a = -5
   148  WHERE id = 1;
   149  
   150  DROP TABLE recover_and_insert;
   151  
   152  RECOVER TABLE recover_and_insert;
   153  
   154  -- make sure we can insert data after recovery
   155  INSERT INTO recover_and_insert(id, a)
   156  VALUES (2, -3);
   157  
   158  -- column null test
   159  
   160  CREATE TABLE `column_is_null`
   161  (
   162      `id` int(11) NOT NULL,
   163      `t`  datetime DEFAULT CURRENT_TIMESTAMP,
   164      PRIMARY KEY (`id`)
   165  ) ENGINE = InnoDB
   166    DEFAULT CHARSET = utf8mb4
   167    COLLATE = utf8mb4_bin;
   168  
   169  INSERT INTO `column_is_null`(id)
   170  VALUES (1),
   171         (2);
   172  UPDATE `column_is_null`
   173  SET t = NULL
   174  WHERE id = 1;