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

     1  drop database if exists `cdc_tiflash_test`;
     2  create database `cdc_tiflash_test`;
     3  use `cdc_tiflash_test`;
     4  
     5  CREATE TABLE multi_data_type
     6  (
     7      id          INT AUTO_INCREMENT,
     8      t_boolean   BOOLEAN,
     9      t_bigint    BIGINT,
    10      t_double    DOUBLE,
    11      t_decimal   DECIMAL(38, 19),
    12      t_bit       BIT(64),
    13      t_date      DATE,
    14      t_datetime  DATETIME,
    15      t_timestamp TIMESTAMP NULL,
    16      t_time      TIME,
    17      t_year      YEAR,
    18      t_char      CHAR,
    19      t_varchar   VARCHAR(10),
    20      t_blob      BLOB,
    21      t_text      TEXT,
    22      t_enum      ENUM ('enum1', 'enum2', 'enum3'),
    23      t_set       SET ('a', 'b', 'c'),
    24      t_json      JSON,
    25      PRIMARY KEY (id)
    26  ) ENGINE = InnoDB
    27    DEFAULT CHARSET = utf8
    28    COLLATE = utf8_bin;
    29  
    30  INSERT INTO multi_data_type( t_boolean, t_bigint, t_double, t_decimal, t_bit
    31                             , t_date, t_datetime, t_timestamp, t_time, t_year
    32                             , t_char, t_varchar, t_blob, t_text, t_enum
    33                             , t_set, t_json)
    34  VALUES ( true, 9223372036854775807, 123.123, 123456789012.123456789012, b'1000001'
    35         , '1000-01-01', '9999-12-31 23:59:59', '19731230153000', '23:59:59', 1970
    36         , '测', '测试', 'blob', '测试text', 'enum2'
    37         , 'a,b', NULL);
    38  
    39  ALTER TABLE multi_data_type SET TIFLASH REPLICA 1;
    40  
    41  INSERT INTO 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 ( false, 666, 123.777, 123456789012.123456789012, b'1000001'
    46         , '1000-01-01', '9999-12-31 23:59:59', '19731230153000', '23:59:59', 1970
    47         , '测', '测试', 'blob', '测试text11', 'enum3'
    48         , 'a,b', NULL);
    49  
    50  UPDATE multi_data_type
    51  SET t_bigint = 555
    52  WHERE id = 1;
    53  
    54  SET GLOBAL tidb_row_format_version = 2;
    55  
    56  INSERT INTO multi_data_type( t_boolean, t_bigint, t_double, t_decimal, t_bit
    57                             , t_date, t_datetime, t_timestamp, t_time, t_year
    58                             , t_char, t_varchar, t_blob, t_text, t_enum
    59                             , t_set, t_json)
    60  VALUES ( true, 9223372036875807, 153.123, 123456669012.123456789012, b'1010001'
    61         , '2000-01-01', '9999-12-31 23:59:59', '19731230153000', '23:59:59', 1970
    62         , '测', '测试', 'blob', '测试text', 'enum1'
    63         , 'a,b', '{
    64      "key1": "value1",
    65      "key2": "value2"
    66    }');
    67  
    68  UPDATE multi_data_type
    69  SET t_bigint = 888,
    70      t_json   = '{
    71        "key0": "value0",
    72        "key2": "value2"
    73      }'
    74  WHERE id = 2;