Last active
August 25, 2022 08:15
-
-
Save yoku0825/9482455b42cb2cdfaf52288792441b12 to your computer and use it in GitHub Desktop.
PK AUTO_INCREMENTを比較的後から安全に追加する
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD dummy_uuid VARCHAR(36) DEFAULT NULL UNIQUE; | |
Query OK, 0 rows affected (0.03 sec) | |
Records: 0 Duplicates: 0 Warnings: 0 | |
master [localhost] {msandbox} ((none)) > INSERT INTO d1.t1 VALUES (3, UUID()); | |
Query OK, 1 row affected (0.00 sec) | |
$ ./use_all -e "SELECT * FROM d1.t1" | |
# master | |
num dummy_uuid | |
1 NULL | |
2 NULL | |
3 1e928842-2432-11ed-98fc-fa163f8f0da3 | |
# server: 1: | |
num dummy_uuid | |
2 NULL | |
1 NULL | |
3 1e928842-2432-11ed-98fc-fa163f8f0da3 | |
$ ./use_all -e "SELECT * FROM d1.t1" | |
# master | |
num dummy_uuid | |
1 32567c85-2432-11ed-98fc-fa163f8f0da3 | |
2 NULL | |
3 1e928842-2432-11ed-98fc-fa163f8f0da3 | |
# server: 1: | |
num dummy_uuid | |
2 NULL | |
1 32567c85-2432-11ed-98fc-fa163f8f0da3 | |
3 1e928842-2432-11ed-98fc-fa163f8f0da3 | |
$ ./m -e "UPDATE d1.t1 SET dummy_uuid = UUID() WHERE dummy_uuid IS NULL LIMIT 1" | |
$ ./use_all -e "SELECT * FROM d1.t1" | |
# master | |
num dummy_uuid | |
1 32567c85-2432-11ed-98fc-fa163f8f0da3 | |
2 362f3804-2432-11ed-98fc-fa163f8f0da3 | |
3 1e928842-2432-11ed-98fc-fa163f8f0da3 | |
# server: 1: | |
num dummy_uuid | |
2 362f3804-2432-11ed-98fc-fa163f8f0da3 | |
1 32567c85-2432-11ed-98fc-fa163f8f0da3 | |
3 1e928842-2432-11ed-98fc-fa163f8f0da3 | |
master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD PRIMARY KEY (dummy_uuid); | |
Query OK, 0 rows affected (0.03 sec) | |
Records: 0 Duplicates: 0 Warnings: 0 | |
master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 DROP PRIMARY KEY, ADD id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT; | |
Query OK, 0 rows affected (0.03 sec) | |
Records: 0 Duplicates: 0 Warnings: 0 | |
$ ./use_all -e "SELECT * FROM d1.t1" | |
# master | |
num dummy_uuid id | |
3 1e928842-2432-11ed-98fc-fa163f8f0da3 1 | |
1 32567c85-2432-11ed-98fc-fa163f8f0da3 2 | |
2 362f3804-2432-11ed-98fc-fa163f8f0da3 3 | |
# server: 1: | |
num dummy_uuid id | |
3 1e928842-2432-11ed-98fc-fa163f8f0da3 1 | |
1 32567c85-2432-11ed-98fc-fa163f8f0da3 2 | |
2 362f3804-2432-11ed-98fc-fa163f8f0da3 3 | |
master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 DROP dummy_uuid; | |
Query OK, 0 rows affected (0.02 sec) | |
Records: 0 Duplicates: 0 Warnings: 0 | |
$ ./use_all -e "SELECT * FROM d1.t1" | |
# master | |
num id | |
3 1 | |
1 2 | |
2 3 | |
# server: 1: | |
num id | |
3 1 | |
1 2 | |
2 3 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
master [localhost] {msandbox} ((none)) > LOCK TABLE d1.t1 WRITE; | |
Query OK, 0 rows affected (0.00 sec) | |
master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ORDER BY num; | |
Query OK, 2 rows affected (0.03 sec) | |
Records: 2 Duplicates: 0 Warnings: 0 | |
master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT; | |
Query OK, 0 rows affected (0.02 sec) | |
Records: 0 Duplicates: 0 Warnings: 0 | |
master [localhost] {msandbox} ((none)) > UNLOCK TABLES; | |
Query OK, 0 rows affected (0.00 sec) | |
$ ./use_all -e "SELECT * FROM d1.t1" | |
# master | |
num id | |
1 1 | |
2 2 | |
# server: 1: | |
num id | |
1 1 | |
2 2 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
### Need binlog_format = ROW | |
CREATE TABLE d1.t1_new LIKE d1.t1; | |
ALTER TABLE d1.t1_new ADD id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT; | |
LOCK TABLE d1.t1 WRITE, d1.t1_new WRITE; | |
INSERT INTO d1.t1_new (num) SELECT num FROM d1.t1; | |
RENAME TABLE d1.t1 TO d1.t1_old, d1.t1_new TO d1.t1; | |
UNLOCK TABLES; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment