github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/tests/integration_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  -- make sure `nullable` can be handled by the mounter and mq encoding protocol
    33  INSERT INTO cdc_multi_data_type() VALUES ();
    34  
    35  INSERT INTO cdc_multi_data_type( t_boolean, t_bigint, t_double, t_decimal, t_bit
    36                                 , t_date, t_datetime, t_timestamp, t_time, t_year
    37                                 , t_char, t_varchar, t_blob, t_text, t_enum
    38                                 , t_set, t_json)
    39  VALUES ( true, 9223372036854775807, 123.123, 123456789012.123456789012, b'1000001'
    40         , '1000-01-01', '9999-12-31 23:59:59', '19731230153000', '23:59:59', 1970
    41         , '测', '测试', 'blob', '测试text', 'enum2'
    42         , 'a,b', NULL);
    43  
    44  INSERT INTO cdc_multi_data_type( t_boolean, t_bigint, t_double, t_decimal, t_bit
    45                                 , t_date, t_datetime, t_timestamp, t_time, t_year
    46                                 , t_char, t_varchar, t_blob, t_text, t_enum
    47                                 , t_set, t_json)
    48  VALUES ( true, 9223372036854775807, 678, 321, b'1000001'
    49         , '1000-01-01', '9999-12-31 23:59:59', '19731230153000', '23:59:59', 1970
    50         , '测', '测试', 'blob', '测试text', 'enum2'
    51         , 'a,b', NULL);
    52  
    53  INSERT INTO cdc_multi_data_type(t_boolean)
    54  VALUES (TRUE);
    55  
    56  INSERT INTO cdc_multi_data_type(t_boolean)
    57  VALUES (FALSE);
    58  
    59  INSERT INTO cdc_multi_data_type(t_bigint)
    60  VALUES (-9223372036854775808);
    61  
    62  INSERT INTO cdc_multi_data_type(t_bigint)
    63  VALUES (9223372036854775807);
    64  
    65  INSERT INTO cdc_multi_data_type(t_json)
    66  VALUES ('{
    67    "key1": "value1",
    68    "key2": "value2"
    69  }');
    70  
    71  -- view test
    72  
    73  CREATE TABLE t1
    74  (
    75      id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    76      c1 INT NOT NULL
    77  );
    78  
    79  INSERT INTO t1 (c1)
    80  VALUES (1),
    81         (2),
    82         (3),
    83         (4),
    84         (5);
    85  
    86  CREATE VIEW v1 AS
    87  SELECT *
    88  FROM t1
    89  WHERE c1 > 2;
    90  
    91  -- uk without pk
    92  -- https://internal.pingcap.net/jira/browse/TOOL-714
    93  -- CDC don't support UK is null
    94  
    95  CREATE TABLE uk_without_pk
    96  (
    97      id INT,
    98      a1 INT NOT NULL,
    99      a3 INT NOT NULL,
   100      UNIQUE KEY dex1 (a1, a3)
   101  );
   102  
   103  INSERT INTO uk_without_pk(id, a1, a3)
   104  VALUES (1, 1, 2);
   105  
   106  INSERT INTO uk_without_pk(id, a1, a3)
   107  VALUES (2, 1, 1);
   108  
   109  UPDATE uk_without_pk
   110  SET id = 10,
   111      a1 = 2
   112  WHERE a1 = 1;
   113  
   114  UPDATE uk_without_pk
   115  SET id = 100
   116  WHERE a1 = 10;
   117  
   118  UPDATE uk_without_pk
   119  SET a3 = 4
   120  WHERE a3 = 1;
   121  
   122  -- bit column
   123  -- Test issue: TOOL-1346
   124  
   125  CREATE TABLE binlog_insert_bit
   126  (
   127      a BIT(1) PRIMARY KEY,
   128      b BIT(64)
   129  );
   130  
   131  INSERT INTO binlog_insert_bit
   132  VALUES (0x01, 0xffffffff);
   133  
   134  UPDATE binlog_insert_bit
   135  SET a = 0x00,
   136      b = 0xfffffffe;
   137  
   138  -- recover test
   139  -- Test issue: TOOL-1407
   140  CREATE TABLE recover_and_insert
   141  (
   142      id INT PRIMARY KEY,
   143      a  INT
   144  );
   145  
   146  INSERT INTO recover_and_insert(id, a)
   147  VALUES (1, -1);
   148  
   149  UPDATE recover_and_insert
   150  SET a = -5
   151  WHERE id = 1;
   152  
   153  DROP TABLE recover_and_insert;
   154  
   155  RECOVER TABLE recover_and_insert;
   156  
   157  -- make sure we can insert data after recovery
   158  INSERT INTO recover_and_insert(id, a)
   159  VALUES (2, -3);
   160  
   161  -- column null test
   162  
   163  CREATE TABLE `column_is_null`
   164  (
   165      `id` int(11) NOT NULL,
   166      `t`  datetime DEFAULT CURRENT_TIMESTAMP,
   167      PRIMARY KEY (`id`)
   168  ) ENGINE = InnoDB
   169    DEFAULT CHARSET = utf8mb4
   170    COLLATE = utf8mb4_bin;
   171  
   172  INSERT INTO `column_is_null`(id)
   173  VALUES (1),
   174         (2);
   175  UPDATE `column_is_null`
   176  SET t = NULL
   177  WHERE id = 1;