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 5.6 (Generally Available (GA) Releases) インストール

▼ phpMyAdmin 4.2.11 で表示すると以下のようになります


※ 5.6.11 と 5.6.17 を利用しましたが、手順は同じです ( 2013-05-05 )。
( 5.6.17 で、ODBC ドライバがインストールされなかったので、単体でインストールしました )

▼ ダウンロード
Download MySQL Community Server( 最新 )
MySQL Community Server (Archived Versions)


もう二度ほどインストール(5.6.11)していますし、学生に同時に20台くらいインストールさせました。殆ど選択肢は無く、途中で root のパスワードを入力するぐらいです。アプリケーション側で SHIFT_JIS しか対応していない場合は、同時にインストールされる ODBC ドライバの設定で sjis を選択してやれば良いです。( cp932 のほうがいいかもしれません )
関連する記事

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


ODBC 接続テストの DB は mysql で、登録したパスワードを root と入力すればコンボボックスに DB がいくつか表示されますので選択します。二つほどサンプル DB が登録済みですが全て英文です。

Windows インストーラの但し書き
MySQLのインストーラは32ビットですが、 32ビットと64ビットの両方のバイナリがインストールされます。

なので、32ビットを今からインストールしますが、あとから 64ビットの PC でもインストールして動作確認する予定です。

mysql-installer-community-5.6.11.0.msi



❶ この次はお決まりのライセンスのアクセプトなので、チェックして次へ。

❷ 次は Exexute をクリックすると勝手にチェックして『次へ』となるのでそれから次へ

▼ 結局こうなる。


❸ 選択肢が出ますが、プログラマのテスト用だと普通はこのままで。



❹ 必要なものが揃っているか確認が出ます。

※ これを調整するには戻って『カスタム』で行います


❺ ここは、最終的にインストールされるものの一覧なので『Execute』します



❻ 問題無い場合は以下のようにインストールが完了しますので『次へ』



❼ ここ以降で初期設定ですが、パスワード以外は特別なものはありません。

▼ クリックすると大きく表示します


❽ 『パスワードが弱い』とか怒られますが、気にしないで次へ。

▼ クリックすると大きく表示します


どうしてもサービス名を変えたい場合は、ここで変更。チェックボックスは後からサービスの設定で『手動』にするので、ここでチェックを外さなくてもいいですが、ここでは外してみました。

▼ クリックすると大きく表示します


この後全てインストールされて、MySQL Workbench CE 5.2.47 が起動されますが、これを使うほどの事は通常しないので、終了させて下さい。

MySQL56 は『手動』で『開始』されますので、既存のソフトで接続すればすぐ使えます。ODBC アドミニストレータには、ODBC ドライバが登録されているので MySQL として DSN を作成します。( 通常のクライアントアプリならば、ユーザ DSN でかまいませんが、システム DSN に作っても動作します )





コマンドプロンプトでの確認

mysql.exe でログイン( mysql -u root -p )して、show variables like 'character\_set\_%' を実行すると以下のように表示され、コマンドプロンプトではクライアントが CP932(SHIFT_JIS) である事が解ります。

mysql.exe の インストールパス例 => "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe"



mysql -u root --default-character-set=utf8 -p でログインしてやると、上の cp932 は utf8 に変わります。


SQLの窓 Build C++ で動作確認

ダウンロード

解凍して、WinOfSql.exe を実行して下さい。M ボタンから DSN と mysql と root と パスワード を入力すると、とりあえずテーブルの一覧が出て動作確認になります。

メニューからデータベース一覧を実行すると、下段に一覧が表示されるので、ダブルクリックすると DB 名がクリップボードにコピーされるのでその DB で接続してみると、サンプル DB の中身が見れます。





sakila データベースに接続

テーブルをダブルクリックすると、行データが表示されます。



actor テーブルは、主キーを持っているので、そのままデータを更新できます。メニューの『編集』から『更新』を選択すると更新されます。



DSN で sjis を設定しているので、日本語の列名を使用できます。



下段で、右クリックして『列追加』で追加できます。





関連する記事

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

引数の無い使用方法

select LAST_INSERT_ID()

MySQL の LAST_INSERT_ID 関数は、select LAST_INSERT_ID() として同一接続内で、最後に更新された AUTO_INCREMENT オプションを持つ列に対する最後の更新結果の値を戻します。

返される値は、同一接続内でひとつなので、直前に更新された表が保持していた値に依存します。

但し、単一の INSERT 文を使用して複数の行をインサートする場合、LAST_INSERT_ID() は、最初の インサートされた行のみに対して生成された値を戻します。これは、他のサーバに対して同じ INSERT 文を簡単に再現できるようにするためです。


引数のある使用方法

update コントロールマスタ
   set 売上伝票 = LAST_INSERT_ID( 売上伝票+1 )
where キー = '1'

update 文で LAST_INSERT_ID( 列名+1 ) を使って、整数型の列をカウントアップします。これによって、現在の『整数型の列の値』を取得する事ができます。( select LAST_INSERT_ID() で取得します )

これらは、更新と取得が同時にシステム側で行われる事になるので、テーブルをロックして更新と取得とロック開放という通常の処理の代替として簡単に処理ができます。

関連する記事

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

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

以下は一般的なテーブル作成で、列のオプションとして AUTO_INCREMENT を指定し、表のオプションとして AUTO_INCREMENT の初期値を設定しています。

CREATE TABLE `自動主キー` (
	`コード` INT AUTO_INCREMENT
	,`メッセージ` VARCHAR(100)
	,PRIMARY KEY(`コード`)
) AUTO_INCREMENT = 1000

● AUTO_INCREMENT は整数タイプにのみ対応します
● インデックスされた AUTO_INCREMENT カラムに NULL (推奨) か 0 の値を挿入すると、カラムは次のシーケンス値に設定されます
● 行の挿入後に AUTO_INCREMENT 値を取得するには、 LAST_INSERT_ID() SQL 機能を使用します
● 各テーブルに AUTO_INCREMENT カラムは1つだけ存在する事ができます
● インデックスされる必要があります
● DEFAULT 値を持つ事はできません
● AUTO_INCREMENT カラムは正数のみを含んでいる時だけ正しく機能します
● SERIAL 属性は BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE のエイリアスです
● AUTO_INCREMENT 値をリセットする為に ALTER TABLE tbl_name AUTO_INCREMENT = n を実行しますが、その値は、現在カラム内にある最大値よりも小さく設定する事はできません
● AUTO_INCREMENT カラムを追加する為には、以下のような SQL を実行します

ALTER TABLE テーブル名
	ADD 列名 INT UNSIGNED NOT NULL AUTO_INCREMENT
	,ADD PRIMARY KEY( 列名 )

● 他の属性列を変更する事もできます

ALTER TABLE テーブル名
	CHANGE COLUMN 列名
	列名 INT UNSIGNED NOT NULL AUTO_INCREMENT
	,ADD INDEX ( 列名 )

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

MySQL で datetime 型に入っているデータを使って、24時間未満の経過時間を取得するのには、TIMEDIFF 関数を使います。

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

しかし、プログラマから実際に渡すのは文字列である事が殆どなので、TIMEDIFF を使う前提条件として、CAST, STR_TO_DATE, GET_FORMAT, DATE_FORMAT 関数が必要になります。

MySQL 5.1 リファレンスマニュアル :: 11.5 日付時刻関数

CAST と STR_TO_DATE は、'(シングルクォート)で表現された日付データを実際の日付データとして MySQL に認識させる為に使用します。STR_TO_DATE 関数を使用する場合は、文字列のフォーマットに合わせてフォーマット文字列を用意する必要があります。また、その文字列をある程度自動で取得する方法が、GET_FORMAT 関数です( 使える場面は限定的ですが )。そして、TIMEDIFF の結果として意味のある部分は時刻部分なので、DATE_FORMAT 関数で時刻部分のみを取り出すようにしいます。

select
   DATE_FORMAT(
     TIMEDIFF(
        STR_TO_DATE('2014/10/12 16:27:43', '%Y/%m/%d %H:%i:%s'),
        STR_TO_DATE('2014/10/11 16:27:44', '%Y/%m/%d %H:%i:%s')
     ), '%H:%i:%s' ) as 経過時間1,

   GET_FORMAT(DATETIME,'JIS') as 標準フォーマット,

   DATE_FORMAT(
      TIMEDIFF(
         STR_TO_DATE('2014-10-12 16:27:43', GET_FORMAT(DATETIME,'JIS')),
         STR_TO_DATE('2014-10-11 16:27:44', GET_FORMAT(DATETIME,'JIS'))
    ), '%H:%i:%s' ) as 経過時間2,

   DATE_FORMAT(
      TIMEDIFF(
         CAST('2014-10-12 16:27:43' as datetime),
         CAST('2014-10-11 16:27:44' as datetime)
    ), '%H:%i:%s' ) as 経過時間3

上の SQL の実行結果は以下のようになります

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

以下のような単純な表を考えた場合、当日の勤務時間の計算としては、TIMEDIFF 関数を使います

create table `出退勤トラン` (
	`社員コード` varchar(4)
	,`日時` date
	,`出勤` datetime
	,`退勤` datetime
	,primary key(`社員コード`)
)

TIMEDIFF と DATEDIFF は、それぞれ時間と日付に対して演算を行います。結果として TIMEDIFF は、時刻表示で経過時間をあらわすため、23:59:59 より大きい経過を実行しようとするとエラーになります。
DATEDIFF は経過日数を整数で表すのですが、日付部分が変わると、経過時間が 1 秒でも 1 日を返します。

select 
	社員コード,
	退勤,
	出勤,
	TIMEDIFF(退勤,出勤) as 経過時間,
	DATEDIFF(退勤,出勤) as 経過日数
 from 出退勤トラン

▼ SQL 実行の結果

社員コード	退勤	出勤	経過時間	経過日数
0001	2014/10/12 16:27:44	2014/10/11 16:27:45	2014/10/11 23:59:59	1
0002	2014/10/11 16:27:46	2014/10/11 16:27:45	2014/10/11 0:00:01	0
0003	2014/10/12 0:00:01	2014/10/11 16:27:45	2014/10/11 7:32:16	1
0004	2014/10/11 23:59:59	2014/10/11 16:27:45	2014/10/11 7:32:14	0
0005	2014/10/12	2014/10/11 23:59:59	2014/10/11 0:00:01	1

※ 経過時間の日付部分は、SQLを実行した日の日付です