SQLの窓

2016年06月18日


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;




タグ:SQL
【MySQLの最新記事】
posted by lightbox at 2016-06-18 13:38 | MySQL | このブログの読者になる | 更新情報をチェックする
container 終わり

フリーフォントで簡単ロゴ作成
フリーフォントでボタン素材作成
フリーフォントで吹き出し画像作成
フリーフォントではんこ画像作成
ほぼ自由に利用できるフリーフォント
フリーフォントの書体見本とサンプル
画像を大きく見る為のウインドウを開くボタンの作成

CSS ドロップシャドウの参考デモ
BUTTONS (CSS でボタン)
イラストAC
ぱくたそ
写真素材 足成
フリーフォント一覧
utf8 文字ツール
右サイド 終わり
base 終わり