github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/dm/tests/adjust_gtid/data/db1.increment.sql (about)

     1  use adjust_gtid;
     2  insert into t1 (id, name) values (3, 'Eddard Stark');
     3  update t1 set name = 'Arya Stark' where id = 1;
     4  update t1 set name = 'Catelyn Stark' where name = 'catelyn';
     5  
     6  -- test multi column index with generated column
     7  alter table t1 add column info json;
     8  alter table t1 add column gen_id int as (info->'$.id');
     9  alter table t1 add index multi_col(`id`, `gen_id`, ts);
    10  insert into t1 (id, name, info) values (4, 'gentest', '{"id": 123}');
    11  insert into t1 (id, name, info) values (5, 'gentest', '{"id": 124}');
    12  update t1 set info = '{"id": 120}', ts = '2021-05-11 12:02:05' where id = 1;
    13  update t1 set info = '{"id": 121}' where id = 2;
    14  update t1 set info = '{"id": 122}' where id = 3;
    15  
    16  -- test genColumnCache is reset after ddl
    17  alter table t1 add column info2 varchar(40);
    18  insert into t1 (id, name, info) values (6, 'gentest', '{"id": 125, "test cache": false}');
    19  alter table t1 add unique key gen_idx(`gen_id`);
    20  update t1 set name = 'gentestxx' where gen_id = 123;
    21  
    22  insert into t1 (id, name, info) values (7, 'gentest', '{"id": 126}');
    23  update t1 set name = 'gentestxxxxxx', dt = '2021-05-11 12:03:05', ts = '2021-05-11 12:03:05' where gen_id = 124;
    24  -- delete with unique key
    25  delete from t1 where gen_id > 124;
    26  
    27  -- test alter database
    28  -- tidb doesn't support alter character set from latin1 to utf8m64 so we comment this now
    29  -- alter database adjust_gtid CHARACTER SET = utf8mb4;
    30  
    31  -- test decimal type
    32  alter table t1 add column lat decimal(9,6) default '0.000000';
    33  insert into t1 (id, name, info, lat) values (8, 'gentest', '{"id":127}', '123.123');
    34  
    35  -- test bit type
    36  alter table t1 add column bin bit(1) default NULL;
    37  insert into t1 (id, name, info, lat, bin) values (9, 'gentest', '{"id":128}', '123.123', b'0');
    38  insert into t1 (id, name, info, lat, bin) values (10, 'gentest', '{"id":129}', '123.123', b'1');
    39  
    40  -- test bigint, min and max value for bigint/bigint unsigned
    41  alter table t1 add column big1 bigint;
    42  alter table t1 add column big2 bigint unsigned;
    43  insert into t1 (id, name, info, lat, big1, big2) values (11, 'gentest', '{"id":130}', '123.123', -9223372036854775808, 0);
    44  insert into t1 (id, name, info, lat, big1, big2) values (12, 'gentest', '{"id":131}', '123.123', 9223372036854775807, 18446744073709551615);
    45  
    46  -- test with different session time_zone
    47  SET @@session.time_zone = '+07:00';
    48  insert into t1 (id, name, info) values (13, 'tztest', '{"id": 132}');