たまにしか使わないから忘れるMySQLコマンド集メモ
たまーにしか使わないのに、その必要が急に出てきて、調べる時間が勿体無いので、ざっくりここにMySQLのDDLおよびSQLをメモしていきます。
マッスルジーニアス(Muscle Genius) チンニングバー 懸垂マシン ドア枠に引っ掛けるだけ 俺のドアジム? MG-DG02
- 出版社/メーカー: マッスルジーニアス(Muscle Genius)
- メディア: スポーツ用品
- この商品を含むブログを見る
- テーブル名変更
- プライマリーキー
- プライマリーキーの削除
- 索引
- キー削除
- カラムの追加
- カラム名変更
- カラム定義(属性)変更
- カラム削除
- Jsonの要素を仮想カラム化する(MySQL 5.7 - )
- enum型の定義
- enum型への値追加の(ALTER TABLE)
- テーブルの名前変更
- テーブル定義をコピーする
- 定義のデータも完全にコピーしたい
- INSERT SELECT JOIN
- INSERT SELECT JOINで差分のみINSERTする(DIFF INSERT)
- JOIN UPDATE
- 集計結果を横倒しする
- ストアドプロシージャ、ストアドファンクションの雛形
- mysqlのバックアップ(mysqldump)
- mysql障害時のリカバリ方法(ロールフォワード回復)
- レコードの更新日を自動的にセットする
- グループ毎に連番を振る
テーブル名変更
RENAME TABLE tablea TO tableb ;
もしくは
ALTER TABLE tablea RENAME TO tableb ;
プライマリーキー
ALTER TABLE table_a ADD PRIMARY KEY (col1,col2) ;
プライマリーキーの削除
ALTER TABLE table_a DROP PRIMARY KEY ;
索引
- ユニーク
CREATE UNIQUE INDEX ix01 ON table_a (col1,col2) ;
- 非ユニーク
CREATE INDEX ix01 ON table_a (col1,col2) ;
キー削除
DROP INDEX ix01 ON table_a ;
カラムの追加
ALTER TABLE tab1 ADD COLUMN new_col varchar(20) NOT NULL DEFAULT '' AFTER col1;
カラム名変更
ALTER TABLE tab1 CHANGE COLUMN old_column new_col varchar(20) NOT NULL DEFAULT '';
カラム定義(属性)変更
カラム名は変更しないで、属性を変更する方法
ALTER TABLE tab1 MODIFY COLUMN new_col varchar(100) NOT NULL DEFAULT '';
カラム削除
ALTER TABLE tab1 DROP COLUMN new_col ;
Jsonの要素を仮想カラム化する(MySQL 5.7 - )
ALTER TABLE table_a ADD COLUMN Rank INTEGER GENERATED ALWAYS AS (json_unquote(json_extract(`json_value`,'$.rank'))) VIRTUAL ;
enum型の定義
CREATE TABLE IF NOT EXISTS tab ( id int not null, name ENUM ('abc', 'def', 'ghi') ) ;
enum型への値追加の(ALTER TABLE)
ちなみに値の変更については、変更前の値が入っているとできません
ALTER TABLE tab MODIFY name ENUM ('abc', 'def','ghi','zzz') ;
テーブルの名前変更
table_a のテーブル名を table_b に変更する
rename table table_a to table_b ;
テーブル定義をコピーする
定義は完全にコピーされるがデータはコピーされてない
CREATE TABLE table_b LIKE table_a ;
データを含めて、コピーする
ただし、こちらは、索引等の定義は、コピーされないので注意
CREATE TABLE table_b SELECT * FROM table_a ;
定義のデータも完全にコピーしたい
結局のところ、こちらのほうが、完全にいけます
CREATE TABLE IF NOT EXISTS table_b LIKE table_a ; INSERT INTO table_b SELECT * FROM table_a ;
INSERT SELECT JOIN
INSERT INTO table_a ( pkey, col1, col2, col3, b_value ) SELECT b.col1 || b.col2 || c.col3 AS pkey, b.col1 AS col1, a.col2 AS col2, a.col3 AS col3 FROM table_a a INNER JOIN table_b b ON a.col1 = b.col1 AND a.col2 = b.col2 ;
INSERT SELECT JOINで差分のみINSERTする(DIFF INSERT)
table_bからtable_aにないレコードのみインサートする(差分INSERT) たまにバッチ処理などで利用したりします LEFT JOINがポイントです
INSERT INTO table_a ( pkey, col1, col2, col3, ) SELECT b.pkey, b.col1, b.col2, b.col3 FROM table_b b LEFT JOIN table_a a ON b.pkey = a.pkey WHERE a.pkey IS NULL ;
JOIN UPDATE
JOINしながら、テーブルをUPDATEする!!
UPDATE table_a a INNER JOIN table_v b ON b.SkeyTab = 'artists' AND b.SkeyId = a.ID AND b.TkeyTab = 'artists' SET a.UUID = b.TkeyId WHERE a.UUID IS NULL OR a.UUID = '' ;