VARCHAR型のデータ追加の重複防止方法について

Question

CREATE TABLE `user`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`custom_id` VARCHAR(16),
	`name` VARCHAR(32),
	PRIMARY KEY (`id`)
)Type=MyISAM;

-- 上記のテープルを作成してデータを何件が入れる
-- custom_id は型がvarcharだが数値が入る

SELECT custom_id FRO user ORDER BY custom_id DESC LIMIT 1

-- 上記クエリより値の大きいcustom_idを取得

取得したcustom_idよりデータを追加する際にPHPで

$sql = "INSERT INTO user SET(custom_id,name) VALUES('".($custom_id + 1)."', '名無し太郎');";

上記クエリでデータを追加しています。

諸事情により、custom_idの型を変える事ができないのと、
MySQLの設定でAUTO INCRIMENTやユニークキーの設定ができません。

上記の処理が複数同時に実行されてもcustom_idが重複しない処理方法について情報がありましたらご提供をお願いします。

Answer 1

いちおう、SELECT FOR UPDATEでチェックしてからINSERTを1トランザクション内で行うことで、重複しないようにはできるかもしれません。

BEGIN;
SELECT * FROM user WHERE custom_id = (生成したID) FOR UPDATE;
-- 上のSELECTでヒットすればロールバックして、IDを再生成してやり直し
INSERT INTO user SET(custom_id,name) VALUES((生成したID) , '名無し太郎');
COMMIT;

ただし、custom_idにインデックスがない場合、テーブルロックになってしまって、性能はかなり落ちます。

というより、「ユニークキーの設定ができ」ないほうの事情が気になりますので、そちらを解消するほうが、場合によっては適切かもしれません。

Comments

[KiyoshiMotoki]

MyISAM エンジンは SELECT ... FOR UPDATE 構文をサポートしていないはずですよ。

https://dev.mysql.com/doc/refman/5.6/ja/myisam-storage-engine.html
> トランザクション いいえ ロック粒度 テーブル

[maisumakun]

すみません、MyISAMだということを見落としていました。

MyISAMで無理やり実現するとしたらLOCK TABLEを明示的に使ってテーブルロック、という大げさ（かつ、並行性の落ちる）な手段しかなさそうですね。

[KiyoshiMotoki]

返信ありがとうございます。

一応、1クエリで INSERT できる SQL文をヒネリ出すことができたので、回答させていただきました(^^;

Answer 2

若干、無理矢理感がありますが、以下なら 1クエリで INSERT できるため、custom_idの重複を防ぐことができます。

INSERT INTO user
SELECT
  0,
  CAST(IFNULL(MAX(CAST(custom_id AS UNSIGNED)), 0) + 1 AS CHAR),
  '名無し太郎'
FROM user;

実行結果

mysql> CREATE TABLE `user`
    -> (
    ->     `id` INTEGER  NOT NULL AUTO_INCREMENT,
    ->     `custom_id` VARCHAR(16),
    ->     `name` VARCHAR(32),
    ->     PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO user
    -> SELECT
    ->   0,
    ->   CAST(IFNULL(MAX(CAST(custom_id AS UNSIGNED)), 0) + 1 AS CHAR),
    ->   '名無し太郎'
    -> FROM user;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO user
    -> SELECT
    ->   0,
    ->   CAST(IFNULL(MAX(CAST(custom_id AS UNSIGNED)), 0) + 1 AS CHAR),
    ->   '名無し花子'
    -> FROM user;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM user;
+----+-----------+-----------------+
| id | custom_id | name            |
+----+-----------+-----------------+
|  1 | 1         | 名無し太郎      |
|  2 | 2         | 名無し花子      |
+----+-----------+-----------------+
2 rows in set (0.00 sec)

追加したレコードのidは、SELECT LAST_INSERT_ID();で取得できます。
https://dev.mysql.com/doc/refman/5.6/ja/information-functions.html#function_last-insert-id

PHP からの場合、PDO をお使いならPDO::lastInsertId()で、同様の結果を得られます。
http://php.net/manual/ja/pdo.lastinsertid.php

Comments

[Clementine]

ありがとうございます。

丁寧な回答を頂き大変助かりました。