たまにしか使わないから忘れるMySQLコマンド集メモ

たまーにしか使わないのに、その必要が急に出てきて、調べる時間が勿体無いので、ざっくりここにMySQLのDDLおよびSQLをメモしていきます。

マッスルジーニアス(Muscle Genius)　チンニングバー　懸垂マシン　ドア枠に引っ掛けるだけ　　俺のドアジム? MG-DG02

テーブル名変更

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 = ''
;  

集計結果を横倒しする

hit.hateblo.jp

ストアドプロシージャ、ストアドファンクションの雛形

hit.hateblo.jp

mysqlのバックアップ(mysqldump)

hit.hateblo.jp

mysql障害時のリカバリ方法（ロールフォワード回復）

hit.hateblo.jp

レコードの更新日を自動的にセットする

hit.hateblo.jp

グループ毎に連番を振る

hit.hateblo.jp