MySQL で、主キーの作り変えのプロセス / serial では、自動的に インデックスが作成されます

serial は、自動採番列を作成する為に使いますが、その際列名でユニークインデックスが作成されています。しかし、bigint(20) unsigned NOT NULL AUTO_INCREMENT で作成した場合は何も作成されないので、削除しても動作します。

ストーリー

掲示板テーブルを作成しましたが、主キーを自動採番列としているので、一つの掲示板でしか使用する事が出来ないため、掲示板(スレッド)を区別する為の ID として no 列を追加する事が目的です。

現状確認

show create table board で、MySQL に登録されている create table 文を取得します。この時、UNIQUE KEY `row_no` (`row_no`) が定義されている事が解ります。

列追加

alter table `board` add `no` VARCHAR(20) after `row_no` で、元の主キーである row_no の後ろに no 列を追加します。この時、既にデータが投入されている場合は、no の中は null が設定されます。

主キー削除

主キーを作り変える為に、いったん現在の主キーを alter table board drop primary key で削除します。そして、show create table board で定義の変化を確認します。

主キー作成

既にデータが投入されている場合は、no 列が null の為、主キーが作成できません。なので、全ての列のデータに何かデータをセットします( update board set no = '0' )

その後、alter table board add primary key(`row_no`,`no`) で主キーを作成します。

serial で作成されたユニークキーの削除

特に実行する必要はありませんが、それほど必要なインデックスでは無いので削除する場合は、show index from board でインデックス名を確認して、alter table board drop index row_no で削除します。

データの復帰

既に新しいフォーマットで作成されたデータがある場合は、insert into board select * from board2 という　SQL でデータを投入できます。
※ board2 が保存されていた新しいフォーマットのデータ

-- 最初に作成したテーブルのSQL
CREATE TABLE `board` (
  `row_no` serial,
  `body` varchar(1000),
  `subject` varchar(200),
  `from` varchar(20),
  `pdate` datetime,
  `cdate` datetime,
  primary key(`row_no`)
);

show create table board;

CREATE TABLE `board` (
  `row_no` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `body` varchar(1000) DEFAULT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `from` varchar(20) DEFAULT NULL,
  `pdate` datetime DEFAULT NULL,
  `cdate` datetime DEFAULT NULL,
  PRIMARY KEY (`row_no`),
  UNIQUE KEY `row_no` (`row_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 主キーに掲示板IDを追加する為に、no 列を row_no の後に追加
alter table `board` add `no` VARCHAR(20) after `row_no`;

-- 主キーの削除
alter table board drop primary key;

show create table board;

CREATE TABLE `board` (
  `row_no` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `no` varchar(20) DEFAULT NULL,
  `body` varchar(1000) DEFAULT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `from` varchar(20) DEFAULT NULL,
  `pdate` datetime DEFAULT NULL,
  `cdate` datetime DEFAULT NULL,
  UNIQUE KEY `row_no` (`row_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--※ すでにデータがある場合( no にセットするのは適当な文字列 )
update board set no = '0';

-- 主キー作成
alter table board add primary key(`row_no`,`no`);

show create table board;

CREATE TABLE `board` (
  `row_no` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `no` varchar(20) NOT NULL DEFAULT '',
  `body` varchar(1000) DEFAULT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `from` varchar(20) DEFAULT NULL,
  `pdate` datetime DEFAULT NULL,
  `cdate` datetime DEFAULT NULL,
  PRIMARY KEY (`row_no`,`no`),
  UNIQUE KEY `row_no` (`row_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- インデックスの確認
show index from board;

-- インデックスの削除( ここは内容確認の為 )
alter table board drop index row_no;

CREATE TABLE `board` (
  `row_no` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `no` varchar(20) NOT NULL DEFAULT '',
  `body` varchar(1000) DEFAULT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `from` varchar(20) DEFAULT NULL,
  `pdate` datetime DEFAULT NULL,
  `cdate` datetime DEFAULT NULL,
  PRIMARY KEY (`row_no`,`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 保存していたデータの復帰
insert into board select * from board2;

MySQL

MySQL に PIPES_AS_CONCAT を設定して、CSV を出力する SQL

MySQL : DB のデータを簡単に沢山作る方法は inner join

MySQL で、Group by して合算された行内で最新データの明細を再び取り出す SQL

MySQL のコマンドラインの操作

Windows 環境の MySQL から日本語名のテーブルを コマンドプロンプトでエクスポート(mysqldump.exe)する

MySQLの日本語オンラインマニュアル、5.6 とその中のいくつかの重要なリンク

MySQL のコマンドプロンプトからの処理用バッチファイル

MySQL 5.6 (Generally Available (GA) Releases) インストール

MySQL : 引数の無い LAST_INSERT_ID() と 引数のある LAST_INSERT_ID( n ) の使用方法

MySQL における AUTO_INCREMENT 列の設定および詳細

MySQL のプログラムからの経過時間の表現方法( CAST, STR_TO_DATE, GET_FORMAT, DATE_FORMAT, TIMEDIFF )

MySQL で出退勤の列を datetime 型で持った場合、列を使用して DATEDIFF と TIMEDIFF で直接計算

MySQL を UTF-8 環境で作成して、mysqldump.exe でバックアップするバッチファイルのサンプル

MySQL 5.6 : テストデータ自動作成スクリプト

PHP + MySQL : Windows の COM を使った ODBC 経由での接続とデータの取得(SELECT)

VBScript + MySQL : ADO( ODBC ) による接続とデータの取得(SELECT)

PHP + MySQL : Windows における ODBC 経由での接続とデータの取得(SELECT)

PHP + MySQL : PDO_MySQL での接続とデータの取得(SELECT)

PHP + MySQL : MySQL 改良版拡張モジュール mysqli クラスでの接続とデータの取得(SELECT)

PHP + MySQL : PHP 5.5.0 で非推奨の MySQL 関数での接続とデータの取得(SELECT)