SQL – 東京発、雲に乗って

私が開発する場合、氏名などのカナは全角カタカナでバリデーションチェックを行いますが、
とくにチェックされてなかったシステムがあって、
人によってひらがなだったりカタカナだったりばらばらでした。

そのシステムの改修で
カタカナ以外エラーになるようにバリデーションチェックを追加したのですが
それだけではなく、既存のデータをカタカナに統一する必要があります。

スマートな方法がないか調べましたが、結局やったのはこちら。

UPDATE `テーブル名` SET column = REPLACE(column, "が","ガ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぎ","ギ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぐ","グ");
UPDATE `テーブル名` SET column = REPLACE(column, "げ","ゲ");
UPDATE `テーブル名` SET column = REPLACE(column, "ご","ゴ");
UPDATE `テーブル名` SET column = REPLACE(column, "ざ","ザ");
UPDATE `テーブル名` SET column = REPLACE(column, "じ","ジ");
UPDATE `テーブル名` SET column = REPLACE(column, "ず","ズ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぜ","ゼ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぞ","ゾ");
UPDATE `テーブル名` SET column = REPLACE(column, "ば","バ");
UPDATE `テーブル名` SET column = REPLACE(column, "び","ビ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぶ","ブ");
UPDATE `テーブル名` SET column = REPLACE(column, "べ","ベ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぼ","ボ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぱ","パ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぴ","ピ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぷ","プ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぺ","ペ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぽ","ポ");

UPDATE `テーブル名` SET column = REPLACE(column, "あ","ア");
UPDATE `テーブル名` SET column = REPLACE(column, "い","イ");
UPDATE `テーブル名` SET column = REPLACE(column, "う","ウ");
UPDATE `テーブル名` SET column = REPLACE(column, "え","エ");
UPDATE `テーブル名` SET column = REPLACE(column, "お","オ");
UPDATE `テーブル名` SET column = REPLACE(column, "か","カ");
UPDATE `テーブル名` SET column = REPLACE(column, "き","キ");
UPDATE `テーブル名` SET column = REPLACE(column, "く","ク");
UPDATE `テーブル名` SET column = REPLACE(column, "け","ケ");
UPDATE `テーブル名` SET column = REPLACE(column, "こ","コ");
UPDATE `テーブル名` SET column = REPLACE(column, "さ","サ");
UPDATE `テーブル名` SET column = REPLACE(column, "し","シ");
UPDATE `テーブル名` SET column = REPLACE(column, "す","ス");
UPDATE `テーブル名` SET column = REPLACE(column, "せ","セ");
UPDATE `テーブル名` SET column = REPLACE(column, "そ","ソ");
UPDATE `テーブル名` SET column = REPLACE(column, "た","タ");
UPDATE `テーブル名` SET column = REPLACE(column, "ち","チ");
UPDATE `テーブル名` SET column = REPLACE(column, "つ","ツ");
UPDATE `テーブル名` SET column = REPLACE(column, "て","テ");
UPDATE `テーブル名` SET column = REPLACE(column, "と","ト");
UPDATE `テーブル名` SET column = REPLACE(column, "な","ナ");
UPDATE `テーブル名` SET column = REPLACE(column, "に","ニ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぬ","ヌ");
UPDATE `テーブル名` SET column = REPLACE(column, "ね","ネ");
UPDATE `テーブル名` SET column = REPLACE(column, "の","ノ");
UPDATE `テーブル名` SET column = REPLACE(column, "は","ハ");
UPDATE `テーブル名` SET column = REPLACE(column, "ひ","ヒ");
UPDATE `テーブル名` SET column = REPLACE(column, "ふ","フ");
UPDATE `テーブル名` SET column = REPLACE(column, "へ","ヘ");
UPDATE `テーブル名` SET column = REPLACE(column, "ほ","ホ");
UPDATE `テーブル名` SET column = REPLACE(column, "ま","マ");
UPDATE `テーブル名` SET column = REPLACE(column, "み","ミ");
UPDATE `テーブル名` SET column = REPLACE(column, "む","ム");
UPDATE `テーブル名` SET column = REPLACE(column, "め","メ");
UPDATE `テーブル名` SET column = REPLACE(column, "も","モ");
UPDATE `テーブル名` SET column = REPLACE(column, "や","ヤ");
UPDATE `テーブル名` SET column = REPLACE(column, "ゆ","ユ");
UPDATE `テーブル名` SET column = REPLACE(column, "よ","ヨ");
UPDATE `テーブル名` SET column = REPLACE(column, "ら","ラ");
UPDATE `テーブル名` SET column = REPLACE(column, "り","リ");
UPDATE `テーブル名` SET column = REPLACE(column, "る","ル");
UPDATE `テーブル名` SET column = REPLACE(column, "れ","レ");
UPDATE `テーブル名` SET column = REPLACE(column, "ろ","ロ");
UPDATE `テーブル名` SET column = REPLACE(column, "わ","ワ");
UPDATE `テーブル名` SET column = REPLACE(column, "を","ヲ");
UPDATE `テーブル名` SET column = REPLACE(column, "ん","ン");

UPDATE `テーブル名` SET column = REPLACE(column, "が","ガ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぎ","ギ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぐ","グ");

UPDATE `テーブル名` SET column = REPLACE(column, "げ","ゲ");

UPDATE `テーブル名` SET column = REPLACE(column, "ご","ゴ");

UPDATE `テーブル名` SET column = REPLACE(column, "ざ","ザ");

UPDATE `テーブル名` SET column = REPLACE(column, "じ","ジ");

UPDATE `テーブル名` SET column = REPLACE(column, "ず","ズ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぜ","ゼ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぞ","ゾ");

UPDATE `テーブル名` SET column = REPLACE(column, "ば","バ");

UPDATE `テーブル名` SET column = REPLACE(column, "び","ビ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぶ","ブ");

UPDATE `テーブル名` SET column = REPLACE(column, "べ","ベ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぼ","ボ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぱ","パ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぴ","ピ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぷ","プ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぺ","ペ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぽ","ポ");

UPDATE `テーブル名` SET column = REPLACE(column, "あ","ア");

UPDATE `テーブル名` SET column = REPLACE(column, "い","イ");

UPDATE `テーブル名` SET column = REPLACE(column, "う","ウ");

UPDATE `テーブル名` SET column = REPLACE(column, "え","エ");

UPDATE `テーブル名` SET column = REPLACE(column, "お","オ");

UPDATE `テーブル名` SET column = REPLACE(column, "か","カ");

UPDATE `テーブル名` SET column = REPLACE(column, "き","キ");

UPDATE `テーブル名` SET column = REPLACE(column, "く","ク");

UPDATE `テーブル名` SET column = REPLACE(column, "け","ケ");

UPDATE `テーブル名` SET column = REPLACE(column, "こ","コ");

UPDATE `テーブル名` SET column = REPLACE(column, "さ","サ");

UPDATE `テーブル名` SET column = REPLACE(column, "し","シ");

UPDATE `テーブル名` SET column = REPLACE(column, "す","ス");

UPDATE `テーブル名` SET column = REPLACE(column, "せ","セ");

UPDATE `テーブル名` SET column = REPLACE(column, "そ","ソ");

UPDATE `テーブル名` SET column = REPLACE(column, "た","タ");

UPDATE `テーブル名` SET column = REPLACE(column, "ち","チ");

UPDATE `テーブル名` SET column = REPLACE(column, "つ","ツ");

UPDATE `テーブル名` SET column = REPLACE(column, "て","テ");

UPDATE `テーブル名` SET column = REPLACE(column, "と","ト");

UPDATE `テーブル名` SET column = REPLACE(column, "な","ナ");

UPDATE `テーブル名` SET column = REPLACE(column, "に","ニ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぬ","ヌ");

UPDATE `テーブル名` SET column = REPLACE(column, "ね","ネ");

UPDATE `テーブル名` SET column = REPLACE(column, "の","ノ");

UPDATE `テーブル名` SET column = REPLACE(column, "は","ハ");

UPDATE `テーブル名` SET column = REPLACE(column, "ひ","ヒ");

UPDATE `テーブル名` SET column = REPLACE(column, "ふ","フ");

UPDATE `テーブル名` SET column = REPLACE(column, "へ","ヘ");

UPDATE `テーブル名` SET column = REPLACE(column, "ほ","ホ");

UPDATE `テーブル名` SET column = REPLACE(column, "ま","マ");

UPDATE `テーブル名` SET column = REPLACE(column, "み","ミ");

UPDATE `テーブル名` SET column = REPLACE(column, "む","ム");

UPDATE `テーブル名` SET column = REPLACE(column, "め","メ");

UPDATE `テーブル名` SET column = REPLACE(column, "も","モ");

UPDATE `テーブル名` SET column = REPLACE(column, "や","ヤ");

UPDATE `テーブル名` SET column = REPLACE(column, "ゆ","ユ");

UPDATE `テーブル名` SET column = REPLACE(column, "よ","ヨ");

UPDATE `テーブル名` SET column = REPLACE(column, "ら","ラ");

UPDATE `テーブル名` SET column = REPLACE(column, "り","リ");

UPDATE `テーブル名` SET column = REPLACE(column, "る","ル");

UPDATE `テーブル名` SET column = REPLACE(column, "れ","レ");

UPDATE `テーブル名` SET column = REPLACE(column, "ろ","ロ");

UPDATE `テーブル名` SET column = REPLACE(column, "わ","ワ");

UPDATE `テーブル名` SET column = REPLACE(column, "を","ヲ");

UPDATE `テーブル名` SET column = REPLACE(column, "ん","ン");

実にわかりやすい方法でした。

SQLインジェクション対策の第一歩として、
SQLの実行にはプリペアドステートメントを徹底したほうがよいでしょう。

/* DB接続 */
$db = new PDO('DSN情報', 'ユーザー名', 'パスワード');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/* パラメータ */
$param = array('001', 2);

/* クエリ実行 */
$stmt = $this->db->prepare('select column1 from table1 where id = ? and type = ?');
try {
  $stmt->execute($param);
}
catch(PDOException $e) {
  /* エラー処理 割愛 */
  exit();
}

/* データ取得 */
$val = null;
while($row = $stmt->fetch(PDO::FETCH_NUM)) {
  $val = $row[0];
}

/* DB接続 */

$db = new PDO('DSN情報', 'ユーザー名', 'パスワード');

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/* パラメータ */

$param = array('001', 2);

/* クエリ実行 */

$stmt = $this->db->prepare('select column1 from table1 where id = ? and type = ?');

try {

$stmt->execute($param);

}

catch(PDOException $e) {

/* エラー処理割愛 */

exit();

}

/* データ取得 */

$val = null;

while($row = $stmt->fetch(PDO::FETCH_NUM)) {

$val = $row[0];

}

タグ: SQL

【SQL】1つのDELETE文で複数のテーブルのデータを削除する

【SQL】DBのひらがなをカタカナに一括置換する

【ツール】INSERT文大量生成ツール

【PHP】PDOでプリペアドステートメント