在宅プログラマーの備忘録として2005年に始めたブログです。
MySQLでテーブルにカラムを後から追加するときの書式をすぐ忘れるのでメモ。
テーブルの末尾に追加
|
1 |
ALTER TABLE テーブル名 ADD 追加カラム名 条件; |
テーブルの指定したカラムの後ろに追加
|
1 |
ALTER TABLE テーブル名 ADD 追加カラム名 条件 AFTER 対象カラム名; |
例：
|
1 |
ALTER TABLE table1 ADD column2 int not null default 0 AFTER column1; |
MySQLで複数行の結果を1行にまとめるにはGROUP_CONCAT関数を使用します。
|
1 |
SELECT GROUP_CONCAT(column1 SEPARATOR ',') FROM ～以下略～ |
SEPARATORで区切られた文字列で返ってきます。
どんなときに使うかというと、
たとえば
・USERテーブルにユーザー情報
・ADDRESSテーブルに住所情報
が入っていたとして、各ユーザーは住所が複数登録されている可能性があるとします。
（大手ECサイトでユーザーが住所を複数登録できるというようなイメージです。）
このとき
「全ユーザーの全住所を取得して、ユーザーごとにまとめて表示したい」
となったとき、3つの方法があります。
・SQLでUSERを取得し、USERの1件ずつに対して、またSQLでADDRESSを取得する。
・SQLでサブクエリーを使って全USERの全ADDRESSを取得して、プログラム側でUSERごとにまとめる
・SQLでGROUP_CONCATを使ってUSERごとにADDRESSをまとめた状態で取得する
3番目が一番スマートに取得できますよね。
ただし、SQLがあまり複雑になるとパフォーマンスが低下する場合がありますので、
プログラム側でやったほうがよいケースもあります。
UPDATE文でデータを一括で変更する際に、
「文字列の一部を置換したい」ということがあると思います。
REPLACE関数を使えば簡単です。
|
1 |
UPDATE table1 SET column1 = REPLACE(column1, '置換前', '置換後'); |
MySQLでテーブルの種類がInnoDBの場合、データを削除しても容量は変化しません。
容量を更新するには、削除した後、alter tableを実行します。
|
1 |
alter table t engine=InnoDB; |
MySQLには自動で採番するAUTO_INCREMENTという設定があります。
基本的にはこれを使えば自動的にユニークな番号を採番してくれるのですが、
データを頻繁に削除したり、テーブルをコピーしたりしていると
AUTO_INCREMENTの値が重複したり、過去に削除したデータと同じ値になってしまったりと
いろいろと怖いリスクがあります。
そのリスクを避けるため、私はINSERT文を実行する関数の中で自前で採番するようにしています。
|
1 2 |
/* TABLE1.ROW_NO が採番用カラムとする */ select ifnull(max(ROW_NO), 0) + 1 as max_row_no from TABLE1. |