MySQL Index 確認・追加・削除

MySQL(InnoDB)でのIndex操作についての覚書き。

MySQL Key

Check Keys

Add Key

Add Multiple Column Key

Delete Key

SHOW INDEXES FROM `table_name`;

ALTER TABLE `table_name` ADD INDEX `key_name`(`column_name`);

ALTER TABLE `table_name` ADD INDEX `key_name`(`column_name1`, `column_name2`);

ALTER TABLE `table_name` DROP INDEX `key_name`;

-- 該当カラムにForeign Keyがある場合、Foreign Keyの削除が必要。

MySQL Foreign Key

Check Keys

Add Foreign Key

Add Multiple Column Foreign Key

Delete Foreign Key

SHOW INDEXES FROM `table_name`;

ALTER TABLE `table_nameB` ADD FOREIGN KEY `key_name`(`column_nameB1`)
 REFERENCES `table_nameA` (`column_nameA1`) ON UPDATE CASCADE;

-- table_nameAのcolumn_nameA1にIndexが必要。
-- ON DELETE xxxxx、ON UPDATE xxxxx は状況に合わせて

ALTER TABLE `table_nameB` ADD FOREIGN KEY `key_name`(`column_nameB1`, `column_nameB2`)
 REFERENCES `table_nameA` (`column_nameA1`, `shop_codeA2`) ON UPDATE CASCADE;

-- table_nameAのcolumn_nameA1、column_nameA2にIndexが必要。
-- ON DELETE xxxxx ON UPDATE xxxxx は状況に合わせて

ALTER TABLE `table_name` DROP FOREIGN KEY `key_name`;

MySQL Unique Key

Check Keys

Add Unique Key

Add Multiple Column Unique Key

Delete (Unique) Key

SHOW INDEXES FROM `table_name`;

ALTER TABLE `table_nameB` ADD UNIQUE KEY `key_name`(`column_name`);

ALTER TABLE `table_nameB` ADD UNIQUE KEY `key_name`(`column_name1`, `column_name2`);

ALTER TABLE `table_name` DROP KEY `key_name`;

-- 該当カラムにForeign Keyがある場合、Foreign Keyの削除が必要。