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

     1  USE `test`;
     2  
     3  CREATE TABLE multi_data_type
     4  (
     5      id                   INT AUTO_INCREMENT,
     6      t_tinyint            TINYINT,
     7      t_tinyint_unsigned   TINYINT UNSIGNED,
     8      t_smallint           SMALLINT,
     9      t_smallint_unsigned  SMALLINT UNSIGNED,
    10      t_mediumint          MEDIUMINT,
    11      t_mediumint_unsigned MEDIUMINT UNSIGNED,
    12      t_int                INT,
    13      t_int_unsigned       INT UNSIGNED,
    14      t_bigint             BIGINT,
    15      t_bigint_unsigned    BIGINT UNSIGNED,
    16      t_boolean            BOOLEAN,
    17      t_float              FLOAT(6, 2),
    18      t_double             DOUBLE(6, 2),
    19      t_decimal            DECIMAL(38, 19),
    20      t_char               CHAR,
    21      t_varchar            VARCHAR(10),
    22      c_binary             binary(16),
    23      c_varbinary          varbinary(16),
    24      t_tinytext           TINYTEXT,
    25      t_text               TEXT,
    26      t_mediumtext         MEDIUMTEXT,
    27      t_longtext           LONGTEXT,
    28      t_tinyblob           TINYBLOB,
    29      t_blob               BLOB,
    30      t_mediumblob         MEDIUMBLOB,
    31      t_longblob           LONGBLOB,
    32      t_date               DATE,
    33      t_datetime           DATETIME,
    34      t_timestamp          TIMESTAMP NULL,
    35      t_time               TIME,
    36  --  FIXME: Currently canal-adapter does not handle year types correctly.
    37  --  t_year               YEAR,
    38      t_enum               ENUM ('enum1', 'enum2', 'enum3'),
    39      t_set                SET ('a', 'b', 'c'),
    40  --  FIXME: Currently there will be data inconsistencies.
    41  --  t_bit                BIT(64),
    42      t_json               JSON,
    43      PRIMARY KEY (id)
    44  );
    45  
    46  -- make sure `nullable` can be handled by the mounter and mq encoding protocol
    47  INSERT INTO multi_data_type() VALUES ();
    48  
    49  INSERT INTO multi_data_type( t_tinyint, t_tinyint_unsigned, t_smallint, t_smallint_unsigned, t_mediumint
    50                             , t_mediumint_unsigned, t_int, t_int_unsigned, t_bigint, t_bigint_unsigned
    51                             , t_boolean, t_float, t_double, t_decimal
    52                             , t_char, t_varchar, c_binary, c_varbinary, t_tinytext, t_text, t_mediumtext, t_longtext
    53                             , t_tinyblob, t_blob, t_mediumblob, t_longblob
    54                             , t_date, t_datetime, t_timestamp, t_time
    55                             , t_enum
    56                             , t_set, t_json)
    57  VALUES ( -1, 1, -129, 129, -65536, 65536, -16777216, 16777216, -2147483649, 2147483649
    58         , true, 123.456, 123.123, 123456789012.123456789012
    59         , '测', '测试', x'89504E470D0A1A0A', x'89504E470D0A1A0A', '测试tinytext', '测试text', '测试mediumtext', '测试longtext'
    60         , 'tinyblob', 'blob', 'mediumblob', 'longblob'
    61         , '1977-01-01', '9999-12-31 23:59:59', '19731230153000', '23:59:59'
    62         , 'enum2'
    63         , 'a,b', NULL);
    64  
    65  INSERT INTO multi_data_type( t_tinyint, t_tinyint_unsigned, t_smallint, t_smallint_unsigned, t_mediumint
    66                             , t_mediumint_unsigned, t_int, t_int_unsigned, t_bigint, t_bigint_unsigned
    67                             , t_boolean, t_float, t_double, t_decimal
    68                             , t_char, t_varchar, c_binary, c_varbinary, t_tinytext, t_text, t_mediumtext, t_longtext
    69                             , t_tinyblob, t_blob, t_mediumblob, t_longblob
    70                             , t_date, t_datetime, t_timestamp, t_time
    71                             , t_enum
    72                             , t_set, t_json)
    73  VALUES ( -2, 2, -130, 130, -65537, 65537, -16777217, 16777217, -2147483650, 2147483650
    74         , false, 123.4567, 123.1237, 123456789012.1234567890127
    75         , '2', '测试2', x'89504E470D0A1A0B', x'89504E470D0A1A0B', '测试2tinytext', '测试2text', '测试2mediumtext', '测试longtext'
    76         , 'tinyblob2', 'blob2', 'mediumblob2', 'longblob2'
    77         , '2021-01-01', '2021-12-31 23:59:59', '19731230153000', '22:59:59'
    78         , 'enum1'
    79         , 'a,b,c', '{
    80      "id": 1,
    81      "name": "hello"
    82    }');
    83  
    84  UPDATE multi_data_type
    85  SET t_boolean = false
    86  WHERE id = 1;
    87  
    88  DELETE
    89  FROM multi_data_type
    90  WHERE id = 2;
    91  
    92  CREATE TABLE multi_charset (
    93  	id INT,
    94  	name varchar(128) CHARACTER SET gbk,
    95  	country char(32) CHARACTER SET gbk,
    96  	city varchar(64),
    97  	description text CHARACTER SET gbk,
    98  	image tinyblob,
    99  	PRIMARY KEY (id)
   100  ) ENGINE = InnoDB CHARSET = utf8mb4;
   101  
   102  INSERT INTO multi_charset
   103  VALUES (1, '测试', "中国", "上海", "你好,世界"
   104  	, 0xC4E3BAC3CAC0BDE7);
   105  
   106  INSERT INTO multi_charset
   107  VALUES (2, '部署', "美国", "纽约", "世界,你好"
   108  	, 0xCAC0BDE7C4E3BAC3);
   109  
   110  UPDATE multi_charset
   111  SET name = '开发'
   112  WHERE name = '测试';
   113  
   114  DELETE FROM multi_charset
   115  WHERE name = '部署'
   116  	AND country = '美国'
   117  	AND city = '纽约'
   118  	AND description = '世界,你好';
   119  
   120  CREATE TABLE test_ddl1
   121  (
   122      id INT AUTO_INCREMENT,
   123      c1 INT,
   124      PRIMARY KEY (id)
   125  );
   126  
   127  CREATE TABLE test_ddl2
   128  (
   129      id INT AUTO_INCREMENT,
   130      c1 INT,
   131      PRIMARY KEY (id)
   132  );
   133  
   134  RENAME TABLE test_ddl1 TO test_ddl;
   135  
   136  ALTER TABLE test_ddl
   137      ADD INDEX test_add_index (c1);
   138  
   139  DROP INDEX test_add_index ON test_ddl;
   140  
   141  ALTER TABLE test_ddl
   142      ADD COLUMN c2 INT NOT NULL;
   143  
   144  TRUNCATE TABLE test_ddl;
   145  
   146  DROP TABLE test_ddl2;
   147  
   148  CREATE TABLE test_ddl2
   149  (
   150      id INT AUTO_INCREMENT,
   151      c1 INT,
   152      PRIMARY KEY (id)
   153  );
   154  
   155  CREATE TABLE test_ddl3 (
   156  	id INT,
   157  	名称 varchar(128),
   158  	PRIMARY KEY (id)
   159  ) ENGINE = InnoDB;
   160  
   161  ALTER TABLE test_ddl3
   162  	ADD COLUMN 城市 char(32);
   163  
   164  ALTER TABLE test_ddl3
   165  	MODIFY COLUMN 城市 varchar(32);
   166  
   167  ALTER TABLE test_ddl3
   168  	DROP COLUMN 城市;
   169  
   170  CREATE TABLE 表1 (
   171  	id INT,
   172  	name varchar(128),
   173  	PRIMARY KEY (id)
   174  ) ENGINE = InnoDB;
   175  
   176  RENAME TABLE 表1 TO 表2;
   177  
   178  DROP TABLE 表2;
   179  
   180  CREATE TABLE binary_columns
   181  (
   182      id                   INT AUTO_INCREMENT,
   183      c_binary             binary(255),
   184      c_varbinary          varbinary(255),
   185      t_tinyblob           TINYBLOB,
   186      t_blob               BLOB,
   187      t_mediumblob         MEDIUMBLOB,
   188      t_longblob           LONGBLOB,
   189      PRIMARY KEY (id)
   190  );
   191  
   192  INSERT INTO binary_columns (c_binary, c_varbinary, t_tinyblob, t_blob, t_mediumblob, t_longblob)
   193  VALUES (
   194      x'808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9FA0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF',
   195      x'808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9FA0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF',
   196      x'808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9FA0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF',
   197      x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9FA0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF',
   198      x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9FA0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF',
   199      x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9FA0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF'
   200  );
   201  
   202  INSERT INTO binary_columns (c_binary, c_varbinary, t_tinyblob, t_blob, t_mediumblob, t_longblob)
   203  VALUES (
   204      x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F',
   205      x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F',
   206      x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F',
   207      x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9FA0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF',
   208      x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9FA0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF',
   209      x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9FA0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF'
   210  );