1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
# Test setup.
CREATE TABLE t1 (
c1 INT PRIMARY KEY,
c2 VARCHAR(50),
c3 VARCHAR(50),
c4 VARCHAR(50)
);
INSERT INTO t1 (c1, c2, c3, c4)
VALUES
(1, 'AAA_2', 'AAA_3', 'AAA_4'),
(2, 'BBB_2', 'BBB_3', 'BBB_4'),
(3, 'CCC_2', 'CCC_3', 'CCC_4'),
(4, 'DDD_2', 'DDD_3', 'DDD_4'),
(5, 'EEE_2', 'EEE_3', 'EEE_4');
# Start test.
ALTER TABLE t1 CONVERT TO CHARACTER SET 'utf8mb4',ALGORITHM=COPY;
SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
n_rows database_name table_name
5 test t1
ALTER TABLE t1 ADD COLUMN c5 VARCHAR(15), ALGORITHM = COPY;
SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
n_rows database_name table_name
5 test t1
ALTER TABLE t1 DROP COLUMN c5, ALGORITHM = COPY;
SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
n_rows database_name table_name
5 test t1
ALTER TABLE t1 ADD COLUMN c5 VARCHAR(15), ALGORITHM = COPY;
SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
n_rows database_name table_name
5 test t1
ALTER TABLE t1 DROP COLUMN c5, ALGORITHM = COPY;
SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
n_rows database_name table_name
5 test t1
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (c4), ALGORITHM=COPY;
SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
n_rows database_name table_name
5 test t1
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (c1), ALGORITHM=INPLACE;
SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
n_rows database_name table_name
5 test t1
ALTER TABLE t1 ADD COLUMN c5 VARCHAR(15), ALGORITHM = INPLACE;
SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
n_rows database_name table_name
5 test t1
ALTER TABLE t1 DROP COLUMN c5, ALGORITHM = INPLACE;
SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
n_rows database_name table_name
5 test t1
CREATE TABLE t2 (
c1 INT PRIMARY KEY,
c2 VARCHAR(50),
c3 VARCHAR(50),
c4 VARCHAR(50)
) PARTITION BY RANGE (c1) (
PARTITION p1 VALUES LESS THAN (6),
PARTITION p2 VALUES LESS THAN (11),
PARTITION p3 VALUES LESS THAN (16),
PARTITION p4 VALUES LESS THAN (21)
);
INSERT INTO t2 (c1, c2, c3, c4)
VALUES
(1, 'AAA_2', 'AAA_3', 'AAA_4'),
(2, 'BBB_2', 'BBB_3', 'BBB_4'),
(3, 'CCC_2', 'CCC_3', 'CCC_4'),
(4, 'DDD_2', 'DDD_3', 'DDD_4'),
(5, 'EEE_2', 'EEE_3', 'EEE_4'),
(6, 'FFF_2', 'DDD_3', 'DDD_4'),
(7, 'GGG_2', 'DDD_3', 'DDD_4'),
(8, 'HHH_2', 'DDD_3', 'DDD_4'),
(9, 'III_2', 'DDD_3', 'DDD_4'),
(10, 'JJJ_2', 'DDD_3', 'DDD_4'),
(13, 'KKK_2', 'DDD_3', 'DDD_4'),
(20, 'LLL_2', 'DDD_3', 'DDD_4');
ALTER TABLE t2 CONVERT TO CHARACTER SET 'utf8mb4',ALGORITHM=COPY;
SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name LIKE '%t2%';
n_rows database_name table_name
5 test t2#p#p1
5 test t2#p#p2
1 test t2#p#p3
1 test t2#p#p4
ALTER TABLE t2 ADD COLUMN c5 VARCHAR(15), ALGORITHM = COPY;
SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name LIKE '%t2%';
n_rows database_name table_name
5 test t2#p#p1
5 test t2#p#p2
1 test t2#p#p3
1 test t2#p#p4
ALTER TABLE t2 DROP COLUMN c5, ALGORITHM = COPY;
SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name LIKE '%t2%';
n_rows database_name table_name
5 test t2#p#p1
5 test t2#p#p2
1 test t2#p#p3
1 test t2#p#p4
# Test Cleanup.
DROP TABLE t1;
DROP TABLE t2;
|
