MySQL – データベースの複製

2022-01-04 / tau / コメントする

mysqldumpによる方法

元のデータベースのダンプファイルを作成。

$ mysqldump -u ユーザー名 -p 元DB > ダンプファイル

1	$ mysqldump -u ユーザー名 -p 元DB > ダンプファイル

MySQLで新データベースを作成。またはOSのコマンドラインからmysqladminで新データベースを作成。

$ mysqladmin -u ユーザー名 -p create 新DB

1	$ mysqladmin -u ユーザー名 -p create 新DB

ダンプファイルの内容を新データベースに再現

$ mysql -u ユーザー名 -p 新DB < ダンプファイル

1	$ mysql -u ユーザー名 -p 新DB < ダンプファイル

さくらサーバー – MySQLのアップグレード

2021-12-25 / tau / コメントする

概要

2021年11月、さくらインターネットのカスタマーセンターよりメールがあり、内容はMySQL5.1, 5.5から5.7へのアップグレードを推奨するものだった。5.5のサポート終了やWordPressなどCMSの推奨環境のバージョンアップなどの背景によるもの。

私の環境はさくらレンタルサーバーのスタンダードプラン。後述の経緯もあって少しとまどったが、アップグレードとWordPressの設定を終えた。

状況としては、

MySQL5.5と並んで、既にMySQL5.7が稼働していた
WordPressで使っていたのはMySQL5.5の方

手順の概要は

5.7のWordPress用データベースを削除
5.5のWordPress用データベース以外を削除
アップグレード予約・実行
wp-config.php書き換え

経緯

今回の経緯は以下のとおり。

2016年、さくらレンタルサーバー(スタンダードプラン)の利用開始
- このときにMySQL、WordPressも運用開始
その後2021年6月までの間に5.5→5.7へアップグレード
- アップグレード後も5.5は存続
- このときにWordPressの設定は変更せず
- このため、これ以降も投稿等は5.5の方に記録
2021年11月に5.7へのアップグレード推奨メール
2021年12月に再度アップグレードとWordPress設定変更完了

確認作業

データベース構成

メールを受けてさくらサーバーのコントロールパネルで確認したところ、以下の2つのデータベース(以下、DB)が存在。

mysql57.アカウント名.sakura.ne.jp→MySQL5.7
(mysql2019.db.sakura.ne.jp)
→システムの管理で使われるホスト名
mysql304.db.sakura.ne.jp→MySQL5.5

5.5のデータベースは以下のとおり。

information_schemaはMySQLの管理用
_wp1はWordPress用のDB
_mainと_testの2つはテスト用に自分で作成したもので、特に重要ではない

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| アカウント名_main  |
| アカウント名_test  |
| アカウント名_wp1   |
+--------------------+
4 rows in set (0.05 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| アカウント名_main |

| アカウント名_test |

| アカウント名_wp1 |

+--------------------+

4 rows in set (0.05 sec)

5.7のデータベースは以下のとおり。こちらにもWordPress用の_wp1があり、他のDBも5.5と同じ。

mysql> show databases;
+---------------------------+
| Database                  |
+---------------------------+
| information_schema        |
| アカウント名_main         |
| アカウント名_test         |
| アカウント名_test_utf8mb4 |
| アカウント名_wp1          |
+---------------------------+
6 rows in set (0.07 sec)

mysql> show databases;

+---------------------------+

| Database |

+---------------------------+

| information_schema |

| アカウント名_main |

| アカウント名_test |

| アカウント名_test_utf8mb4 |

| アカウント名_wp1 |

+---------------------------+

6 rows in set (0.07 sec)

WordPress用データベース

MySQL5.5、5.7ともアカウント名_wp1のテーブル構成は同じで、以下のとおり。

mysql> show tables;
+----------------------------+
| Tables_in_アカウント名_wp1 |
+----------------------------+
| wp_ts01_commentmeta        |
| wp_ts01_comments           |
| wp_ts01_links              |
| wp_ts01_options            |
| wp_ts01_postmeta           |
| wp_ts01_posts              |
| wp_ts01_term_relationships |
| wp_ts01_term_taxonomy      |
| wp_ts01_termmeta           |
| wp_ts01_terms              |
| wp_ts01_usermeta           |
| wp_ts01_users              |
+----------------------------+
12 rows in set (0.00 sec)

mysql> show tables;

+----------------------------+

| Tables_in_アカウント名_wp1 |

+----------------------------+

| wp_ts01_commentmeta |

| wp_ts01_comments |

| wp_ts01_links |

| wp_ts01_options |

| wp_ts01_postmeta |

| wp_ts01_posts |

| wp_ts01_term_relationships |

| wp_ts01_term_taxonomy |

| wp_ts01_termmeta |

| wp_ts01_terms |

| wp_ts01_usermeta |

| wp_ts01_users |

+----------------------------+

12 rows in set (0.00 sec)

ここで、5.5と5.7でwp_ts01_postsに以下のような違いがあった。

5.5の投稿数。投稿日付は2016年6月16日～2021年12月13日で確認時点の日付まで。

mysql> select count(*) from wp_ts01_posts;
+----------+
| count(*) |
+----------+
|    12500 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from wp_ts01_posts;

+----------+

| count(*) |

+----------+

| 12500 |

+----------+

1 row in set (0.02 sec)

5.7の投稿数。2016年6月16日～2021年2月8日まで。

mysql> select count(*) from wp_ts01_posts;
+----------+
| count(*) |
+----------+
|     9192 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from wp_ts01_posts;

+----------+

| count(*) |

+----------+

| 9192 |

+----------+

1 row in set (0.01 sec)

5.5に対して5.7の投稿数が少なく、途中までの投稿記録で途切れている。

wp-config.phpのMySQLホスト名を確認すると、以下のようにMySQL5.5の方のホスト名になっていて、MySQL5.7運用開始後もWordPressは5.5を使っている。

........
/** MySQL のホスト名 */
define('DB_HOST', 'mysql304.db.sakura.ne.jp');
........

........

/** MySQL のホスト名 */

define('DB_HOST', 'mysql304.db.sakura.ne.jp');

........

したがってMySQL5.7運用開始後もWordPressでは利用されず、投稿等は5.5の方に記録されていることになる。このことから5.7のWordPress用データベースを破棄して5.5のデータベースに入れ替え、設定変更することにする。

アップグレード

要点

アップグレードの要点は、さくらインターネットのサイトによると以下のとおり。

MySQLの5.5→5.7へのアップグレードは、既に5.7が存在していても可能
アップグレード先の5.7に5.5と同じ名前のDBが存在する場合はアップグレード不可
アップグレードはDBを指定して実行時間を予約
アップグレード後にWordPress等の設定変更が必要

データベースの整理方針

5.5のデータベースをアカウント名_wp1のみ残して削除し、アップグレード先の5.7の同じ名前のDBを削除。

5.5の他のデータベースは自分で作成したテスト用のものだけなので、削除しても問題ない
5.7のアカウント名_wp1は途中までしか投稿記録がなく、不要と判断
双方のinformation_schemaは残置

アップグレードの予約

アップグレードの予約はコントロールパネルで行った。

さくらのコントロールパネルで
- 5.7のアカウント名_wp1を削除
- 5.5のアカウント名_wp1以外を削除
information_schemaはコントロールパネルでは表示されないが5.5、5.7とも残置
12/21の02時にアップグレード予約

アップグレードの確認

翌朝確認したところ、MySQL5.7の方にアカウント名_wp1が作成されており、コンソールで確認したところ昨日までの最新の投稿が記録されていた。

コメントが2件、2時以降についていたが、いずれもspam判定のもので、内容も確認して無視。

WordPressの設定変更

コンソールでも可能だが、さくらインターネットサイトにあるとおりファイルマネージャーで実行。

コントロールパネル左の”Webサイト/データ”→”インストール済み一覧”を選択して、でWordPressのインストール先を確認
“Webサイト/データ”→”ファイルマネージャー”を開いて上記のディレクトリーに移動
wp-config.phpを確認
コンソールでwp-config.phpのコピーを別名で保存(バックアップ用)
ファイルマネージャーでwp-config.phpを右クリック→編集
- MySQLのホスト名の変更
  
  wp-config.php
  PHP
  
  /** MySQL のホスト名 */ define('DB_HOST', 'mysql304.db.sakura.ne.jp'); # 'mysql57.アカウント名.sakura.ne.jp'に変更
  
  1
  2
  3
  
  /** MySQL のホスト名 */
  define('DB_HOST', 'mysql304.db.sakura.ne.jp');
  # 'mysql57.アカウント名.sakura.ne.jp'に変更
- DB名は同じ名前なので変更しない
  
  wp-config.php
  PHP
  
  /** WordPress のためのデータベース名 */ define('DB_NAME', 'アカウント名_wp1');
  
  1
  2
  
  /** WordPress のためのデータベース名 */
  define('DB_NAME', 'アカウント名_wp1');
- パスワードも変更していないのでそのまま
  
  wp-config.php
  PHP
  
  /** MySQL データベースのパスワード */ define('DB_PASSWORD', '********');
  
  1
  2
  
  /** MySQL データベースのパスワード */
  define('DB_PASSWORD', '********');
ファイルマネージャーで”保存”、”閉じる”

WordPressの設定後確認

設定変更後、以下を確認した。

WordPressが正常に表示される
新規投稿を書き込み、投稿が反映されること、その内容がMySQLのDBのテーブルに反映されていることを確認
新規投稿を削除し、削除が反映され、テーブルにも削除経緯が反映されることを確認

MySQL – 月単位の集計～残高もある場合

2021-09-17 / tau / コメントする

例題設定

例題として個人の銀行口座の入出金記録の以下の構造のテーブルを考える。

mysql> DESCRIBE account;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int          | YES  |     | NULL    |       |
| date        | date         | YES  |     | NULL    |       |
| branch      | varchar(3)   | YES  |     | NULL    |       |
| transaction | varchar(20)  | YES  |     | NULL    |       |
| category    | varchar(10)  | YES  |     | NULL    |       |
| withdrawal  | int          | YES  |     | NULL    |       |
| deposit     | int          | YES  |     | NULL    |       |
| balance     | int          | YES  |     | NULL    |       |
| partner     | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql> DESCRIBE account;

+-------------+--------------+------+-----+---------+-------+

+-------------+--------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| withdrawal | int | YES | | NULL | |

| deposit | int | YES | | NULL | |

| balance | int | YES | | NULL | |

+-------------+--------------+------+-----+---------+-------+

9 rows in set (0.00 sec)

ここでidは入出金記録の順番に付されていて、変更されないものとする。すなわち、日・月・年などの単位でグルーピングしたとき、idが最も大きいレコードのbalanceがその期の残高となっている。

合計計算のみの場合

月ごとの合計を計算するだけの場合、年月でグルーピングしてSUM関数を使う。

SELECT
  MAX(DATE_FORMAT(date, '%Y年%m月')) AS 年月,
  SUM(withdrawal) AS 月別出金,
  SUM(deposit) AS 月別入金
FROM account
GROUP BY DATE_FORMAT(date, '%Y%m')
;

SELECT

MAX(DATE_FORMAT(date, '%Y年%m月')) AS 年月,

SUM(withdrawal) AS 月別出金,

SUM(deposit) AS 月別入金

FROM account

GROUP BY DATE_FORMAT(date, '%Y%m')

;

要点は以下の通り。

GROUP BY句で、yyyymm形式でフォーマットされた年月単位でグルーピング
グループごとにSUM関数で出金額と入金額を合計
年月表示はGROUP BY句のフォーマットと異なってもよい
ただしグルーピングされた複数レコード中のどのレコードのdateを使うかを明示するため、MAX関数を使っている。
- こうしないと(オプション指定なしでは)エラーが発生する

各月の残高も含める場合

月ごとの最終取引の抽出

各月の残高がデータに含まれていて、取引がidの昇順であることがわかっている場合、月ごとにグルーピングされたレコードのうちidが最大のレコードがその月の最後の取引になり、そのbalanceが月の残高になる。

そこで、まず各月の最終取引のidを取得する。

SELECT
  MAX(id) AS max_id
FROM account
GROUP BY DATE_FORMAT(date, '%Y%m')
;

SELECT

MAX(id) AS max_id

FROM account

GROUP BY DATE_FORMAT(date, '%Y%m')

;

各月最終取引のレコード取得と残高表示

最終取引のidに一致するレコードのみ元のテーブルから取り出すため、INNER JOINを使う。

SELECT
  id,
  DATE_FORMAT(date, '%Y年%m月') AS 年月,
  balance AS 月末残高
FROM mufj
INNER JOIN (
  SELECT
    MAX(id) AS max_id
  FROM mufj
  GROUP BY DATE_FORMAT(date, '%Y%m')
) AS monthly_final_transaction
ON id = max_id
;

SELECT

id,

DATE_FORMAT(date, '%Y年%m月') AS 年月,

balance AS 月末残高

FROM mufj

INNER JOIN (

SELECT

MAX(id) AS max_id

FROM mufj

GROUP BY DATE_FORMAT(date, '%Y%m')

) AS monthly_final_transaction

ON id = max_id

;

要点は以下の通り。

最終取引を抽出したクエリーをサブクエリ―として、そのidと等しいレコードのみを元のテーブルから抽出している
サブクエリ―にはエイリアスが必須
抽出された最終取引のbalanceを月末残高として表示
SELECT句のidは確認用で、表示目的としては不要

月ごとの入出金額を追加

上記のクエリーに、グルーピングされた月ごとの入出金額を追加する。

SELECT
  DATE_FORMAT(date, '%Y年%m月') AS 年月,
  monthly_withdrawal AS 出金額,
  monthly_deposit AS 入金額,
  balance AS 月末残高
FROM mufj
INNER JOIN (
  SELECT
    MAX(id) AS max_id,
    SUM(withdrawal) AS monthly_withdrawal,
    SUM(deposit) AS monthly_deposit
  FROM mufj
  GROUP BY DATE_FORMAT(date, '%Y%m')
) AS monthly_final_transaction
ON id = max_id
;

SELECT

DATE_FORMAT(date, '%Y年%m月') AS 年月,

monthly_withdrawal AS 出金額,

monthly_deposit AS 入金額,

balance AS 月末残高

FROM mufj

INNER JOIN (

SELECT

MAX(id) AS max_id,

SUM(withdrawal) AS monthly_withdrawal,

SUM(deposit) AS monthly_deposit

FROM mufj

GROUP BY DATE_FORMAT(date, '%Y%m')

) AS monthly_final_transaction

ON id = max_id

;

要点は以下の通り。

月ごとにグルーピングしているサブクエリ―で、SUM関数によって月単位の出金・入金額を計算
それらの値をエイリアスを使って元のテーブルで表示

このクエリーのチェックは、前月残高に当月の入出金額を加減して当月残高となることで確認できる。

MySQL – CSVファイルのインポート

2021-09-17 / tau / コメントする

概要

MySQLでCSVファイルをインポートする手順。

要点は以下の通り。

CSVファイルはUTF-8(BOMなし)で準備する
改行コードに注意
MySQLサーバーとクライアントの両方でローカルのファイル入力の許可が必要
- クライアントはログイン時のオプションで、サーバー側はグローバル変数で設定する

CSVファイルの準備

CSVファイルを準備し、MySQLで読み込む場所に配置する。CSV作成時の型と文字コードに注意。

型に関する注意点

数字だが001のように表現したい場合は文字列として扱う。

Excelの列の形式で適切な型を設定する

日付・時刻形式の空白値に注意。

日付・時刻値の列に空のデータがある場合にMySQLのdate/time/datetimeで読み込むと、NULL値ではなく'0000-00-00'や'0000-00-00 00:00:00'で埋められてしまう

文字コードの注意点

CSVファイルはUTF-8のBOMなしで準備する。

ExcelのシートをCSVファイルとして保存すると、UTF-8(BOMあり)になるので、メモ帳などで開いてBOMなしで保存し直す必要がある

MySQLログイン時のオプション

MySQLにログインするクライアントで、ローカルのファイル入力を許可する必要がある。

ログイン時に以下のオプションのいずれかを付ける

--enebale-local-infile
--local_infile=1またはon

$ mysql -u root -p --enable-local-infile

1	$ mysql -u root -p --enable-local-infile

MySQLログイン後の設定

グローバル変数の確認

SELECTかSHOW GLOBAL VARIABLESでlocal_infileの値を確認。この値が0/OFFの場合は許可されていない。以下のいずれかで確認できる。

SELECT @@local_infile;

mysql> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT @@local_infile;

+----------------+

| @@local_infile |

+----------------+

| 0 |

+----------------+

1 row in set (0.00 sec)

SHOW GLOBAL VARIABLES LIKE 'local_infile';

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| local_infile | OFF |

+---------------+-------+

1 row in set (0.01 sec)

グローバル変数の設定

SET GLOBALでlocal_infileの値を1またはONにセットする。

SET GLOBAL local_infile=1またはON;

mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL local_infile=1;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@local_infile;

+----------------+

| @@local_infile |

+----------------+

| 1 |

+----------------+

1 row in set (0.00 sec)

インポート実行

テーブルの準備

CSVの各カラムに対応した構造で、空のテーブルを準備しておく。

MySQLのtimestamp型の2038年問題に注意。

LOAD DATAの実行

SQLの例

CSVファイルをテーブルにインポートするSQLの例。

LOAD DATA LOCAL INFILE "ファイルパス"
  INTO TABLE テーブル名
  FIELDS
    TERMINATED BY ','
  LINES
    TERMINATED BY "\r\n"
  IGNORE 1 LINES
;

LOAD DATA LOCAL INFILE "ファイルパス"

INTO TABLE テーブル名

FIELDS

TERMINATED BY ','

LINES

TERMINATED BY "\r\n"

IGNORE 1 LINES

;

この例では、WindowsのようにCRLFで改行されたファイルを読み込み、区切り文字がカンマのCSVとして、先頭1行を飛ばして読み込む。

FIELDS

TERMINATED BY: CSV保存時に選んだ区切り文字を指定する。1文字である必要はない。デフォルトは'\t'。
ENCLOSED BY: フィールドを囲み文字を1文字で指定する。デフォルトは''(空)で、フィールドが区切り文字で囲まれていることを期待しない。OPTIONALLYを付けると文字列のみ囲み文字で囲まれていることを期待する。
ESCAPED BY: エスケープ文字を1文字で指定する。エスケープ文字に続く文字がエスケープシーケンスとして解釈される。デフォルトは'\\'で'\'一文字を表している。

LINES

STARTING BY: 指定したprefix以降のみ行として扱い、それ以外はスキップする。prefixは1文字でなくてもよい。デフォルトは''でprefixを設定していない(常に行頭から読み込む)。
TERMINATED BY: 行末の文字列を指定する。デフォルトは'\n'。

IGNORE

IGNORE 数値 lines/rowsで、先頭の指定した数の行／列を読み込まない。

MySQL – timestampの2038年問題

2021-08-30 / tau / コメントする

timestamp型とdatetime型

MySQLの日付・時刻表現にはtimestamp型とdatetime型がある。

MySQLのtimestamp型は内部表現に整数を用いていて、これが32bit精度の場合には、2038-01-19 03:14:07UTCまでしか表現できない。これは日本のタイムゾーンだと2038-01-19 12:14:07JSTに対応する。

一方datetime型は文字列で日時を表現していて、その範囲は1000年～9999年の間とされている。

timestamp型とdatetime型の有効範囲を比較すると以下のとおり。

timestamp	datetime
UTC内部表現で保持	タイムゾーン文字列で保持
1970-01-01 00:00:01UTC～2038-01-19 03:14:07UTC	1000-01-01 00:00:00～9999-12-31 23:59:59

timestamp型の2038年問題

確認環境

timestamp型で上限値より大きな値を登録しようとすると、エラーで登録ができない。このことを確認してみる。環境は以下のとおり。

Windows(64bit版)上のVagrant+VirtualBOXで構築したCentOS7
MySQLのバージョンは8.0.26

$ mysql --version
mysql  Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

1 2	$ mysql --version mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

確認用のテーブルは以下のとおり。

mysql> DESCRIBE test;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int       | YES  |     | NULL    |       |
| time  | timestamp | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> DESCRIBE test;

+-------+-----------+------+-----+---------+-------+

+-------+-----------+------+-----+---------+-------+

| id | int | YES | | NULL | |

+-------+-----------+------+-----+---------+-------+

2 rows in set (0.00 sec)

上限を超える値の登録は不可

まず上限値一杯の日時を登録してみる。UTCに対して日本のタイムゾーンで表現した2038年1月19日12時14分07秒を登録すると、問題なく登録される。

mysql> INSERT INTO test VALUES(1, '2038-01-19 12:14:07');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    1 | 2038-01-19 12:14:07 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO test VALUES(1, '2038-01-19 12:14:07');

Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test;

+------+---------------------+

| id | time |

+------+---------------------+

| 1 | 2038-01-19 12:14:07 |

+------+---------------------+

1 row in set (0.00 sec)

次に上限値に1秒を加えた日時を登録しようとすると、"Incorrect datetime value"となって登録できない。

mysql> INSERT INTO test VALUES(1, '2038-01-19 12:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 12:14:08' for column 'time' at row 1

1 2	mysql> INSERT INTO test VALUES(1, '2038-01-19 12:14:08'); ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 12:14:08' for column 'time' at row 1

timestamp型に対する加算の場合は9999年まで

次に登録された上限一杯の値に対してインターバルを加えていき、問題が生じるまでその境界を探していった。

その結果、9999年12月31日23時59分59秒999999…までは登録可能だが、10000年1月1日0時0分0秒に達することはできず、その場合にはNULLとなることがわかった。

mysql> SELECT time + INTERVAL 60*60*24*365*7967.23695944317605 SECOND FROM test;
+---------------------------------------------------------+
| time + INTERVAL 60*60*24*365*7967.23695944317605 SECOND |
+---------------------------------------------------------+
| 9999-12-31 23:59:59.999999                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT time + INTERVAL 60*60*24*365*7967.23695944317606 SECOND FROM test;
+---------------------------------------------------------+
| time + INTERVAL 60*60*24*365*7967.23695944317606 SECOND |
+---------------------------------------------------------+
| NULL                                                    |
+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT time + INTERVAL 60*60*24*365*7967.23695944317605 SECOND FROM test;

+---------------------------------------------------------+

| time + INTERVAL 60*60*24*365*7967.23695944317605 SECOND |

+---------------------------------------------------------+

| 9999-12-31 23:59:59.999999 |

+---------------------------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT time + INTERVAL 60*60*24*365*7967.23695944317606 SECOND FROM test;

+---------------------------------------------------------+

| time + INTERVAL 60*60*24*365*7967.23695944317606 SECOND |

+---------------------------------------------------------+

| NULL |

+---------------------------------------------------------+

1 row in set, 1 warning (0.00 sec)

32bitの上限を超えた後も値は保持されているが、その次の上限はdatetime型の上限と一致している。以下の何れかと推測される。

内部的には64bit表現だが、入力時には32bitの上限で、演算時にはdatetime型の上限で抑えている
内部的に32bit表現だが、その上限を超えたときにはdatetime型に内部的に切り替えている

まとめ

MySQLのtimestamp型は、2038年の上限値を超える値を登録できない
timestamp型に対する加算を行った場合、datetime型と同じ上限まで値を保持できる
- 加算後にdatetime型の上限を超えた場合の値はNULLになる

64bit整数の場合はtimestampも西暦3000億年弱まで扱い可能だが、一部が64bitシステムでも、データベース、言語、フレームワークの全てが対応していないとまずそうなので、日付時刻を扱う際はdatetime型としておくのが安全なようだ。

MySQL – 並べ替え～ORDER BY

2021-08-22 / tau / コメントする

準備

以下のテーブルで確認する。

mysql> SELECT * FROM test_order;
+------+------+
| num1 | num2 |
+------+------+
|    1 |   10 |
|    1 |   20 |
|    1 |   30 |
|    2 |   10 |
|    2 |   20 |
|    2 |   30 |
|    3 |   10 |
|    3 |   20 |
|    3 |   30 |
+------+------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM test_order;

+------+------+

| num1 | num2 |

+------+------+

| 1 | 10 |

| 1 | 20 |

| 1 | 30 |

| 2 | 10 |

| 2 | 20 |

| 2 | 30 |

| 3 | 10 |

| 3 | 20 |

| 3 | 30 |

+------+------+

9 rows in set (0.00 sec)

指定方法

ORDER BYで指定したカラムをキーにして並べ替える。デフォルトは昇順でASCを指定したのと同じ。

SELECT カラム群 FROM テーブル ORDER BY カラム [ASC/DESC];

以下の例では、2つ目のnum2で昇順にソートしている。

mysql> SELECT * FROM test_order ORDER BY num2;
+------+------+
| num1 | num2 |
+------+------+
|    1 |   10 |
|    2 |   10 |
|    3 |   10 |
|    1 |   20 |
|    2 |   20 |
|    3 |   20 |
|    1 |   30 |
|    2 |   30 |
|    3 |   30 |
+------+------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM test_order ORDER BY num2;

+------+------+

| num1 | num2 |

+------+------+

| 1 | 10 |

| 2 | 10 |

| 3 | 10 |

| 1 | 20 |

| 2 | 20 |

| 3 | 20 |

| 1 | 30 |

| 2 | 30 |

| 3 | 30 |

+------+------+

9 rows in set (0.00 sec)

降順でソートする場合はDESCを指定。以下はnum2カラムの内容で降順にソートしている。

mysql> SELECT * FROM test_order ORDER BY num2 DESC;
+------+------+
| num1 | num2 |
+------+------+
|    1 |   30 |
|    2 |   30 |
|    3 |   30 |
|    1 |   20 |
|    2 |   20 |
|    3 |   20 |
|    1 |   10 |
|    2 |   10 |
|    3 |   10 |
+------+------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM test_order ORDER BY num2 DESC;

+------+------+

| num1 | num2 |

+------+------+

| 1 | 30 |

| 2 | 30 |

| 3 | 30 |

| 1 | 20 |

| 2 | 20 |

| 3 | 20 |

| 1 | 10 |

| 2 | 10 |

| 3 | 10 |

+------+------+

9 rows in set (0.00 sec)

複数カラムでのソート

ORDER BYで複数カラムを指定すると、指定したカラムの順でネストの外側のようにしてソートする。

以下の例では、num1で降順ソートし、その中でnum2で降順ソートしている。

mysql> SELECT * FROM test_order ORDER BY num1 DESC, num2 DESC;
+------+------+
| num1 | num2 |
+------+------+
|    3 |   30 |
|    3 |   20 |
|    3 |   10 |
|    2 |   30 |
|    2 |   20 |
|    2 |   10 |
|    1 |   30 |
|    1 |   20 |
|    1 |   10 |
+------+------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM test_order ORDER BY num1 DESC, num2 DESC;

+------+------+

| num1 | num2 |

+------+------+

| 3 | 30 |

| 3 | 20 |

| 3 | 10 |

| 2 | 30 |

| 2 | 20 |

| 2 | 10 |

| 1 | 30 |

| 1 | 20 |

| 1 | 10 |

+------+------+

9 rows in set (0.00 sec)

順番を入れ替えた例。まずnum2で降順ソートしてから、その中でnum1でソートしている。

mysql> SELECT * FROM test_order ORDER BY num2 DESC, num1 DESC;
+------+------+
| num1 | num2 |
+------+------+
|    3 |   30 |
|    2 |   30 |
|    1 |   30 |
|    3 |   20 |
|    2 |   20 |
|    1 |   20 |
|    3 |   10 |
|    2 |   10 |
|    1 |   10 |
+------+------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM test_order ORDER BY num2 DESC, num1 DESC;

+------+------+

| num1 | num2 |

+------+------+

| 3 | 30 |

| 2 | 30 |

| 1 | 30 |

| 3 | 20 |

| 2 | 20 |

| 1 | 20 |

| 3 | 10 |

| 2 | 10 |

| 1 | 10 |

+------+------+

9 rows in set (0.00 sec)

MySQL – JOIN – INNER/LEFT/RIGHT/FULL OUTER

2021-08-21 / tau / コメントする

準備

以下の2つのテーブルを準備する。

students: 学生IDと学生名を保存。
courses: 学科IDと学科名を保存。

studentsテーブルの作成とデータ登録のクエリー。

CREATE TABLE students (
  id INT,
  name VARCHAR(10)
);

INSERT INTO students VALUES
  (1, '安藤'),
  (2, '伊藤'),
  (3, '宇藤'),
  (4, '江藤');

CREATE TABLE students (

id INT,

name VARCHAR(10)

);

INSERT INTO students VALUES

(1, '安藤'),

(2, '伊藤'),

(3, '宇藤'),

(4, '江藤');

実行結果。

mysql> SELECT * FROM students;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 安藤   |
|    2 | 伊藤   |
|    3 | 宇藤   |
|    4 | 江藤   |
+------+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM students;

+------+--------+

| id | name |

+------+--------+

| 1 | 安藤 |

| 2 | 伊藤 |

| 3 | 宇藤 |

| 4 | 江藤 |

+------+--------+

4 rows in set (0.00 sec)

coursesテーブルの作成とデータ登録のクエリー。

CREATE TABLE courses (
  id INT,
  name VARCHAR(10)
);

INSERT INTO courses VALUES
  (10, '数学'),
  (11, '物理学'),
  (12, '化学'),
  (13, '天文学');

CREATE TABLE courses (

id INT,

name VARCHAR(10)

);

INSERT INTO courses VALUES

(10, '数学'),

(11, '物理学'),

(12, '化学'),

(13, '天文学');

実行結果。

mysql> SELECT * FROM courses;
+------+-----------+
| id   | name      |
+------+-----------+
|   10 | 数学      |
|   11 | 物理学    |
|   12 | 化学      |
|   13 | 天文学    |
+------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM courses;

+------+-----------+

| id | name |

+------+-----------+

| 10 | 数学 |

| 11 | 物理学 |

| 12 | 化学 |

| 13 | 天文学 |

+------+-----------+

4 rows in set (0.00 sec)

JOIN～総当たり

FROM句のテーブルに対して単にJOINでテーブルを指定すると、2つのテーブルの全要素の組み合わせが得られる。

SELECT students.name, courses.name
FROM students
JOIN courses;

SELECT students.name, courses.name

FROM students

JOIN courses;

実行結果。

+--------+-----------+
| name   | name      |
+--------+-----------+
| 江藤   | 数学      |
| 宇藤   | 数学      |
| 伊藤   | 数学      |
| 安藤   | 数学      |
| 江藤   | 物理学    |
| 宇藤   | 物理学    |
| 伊藤   | 物理学    |
| 安藤   | 物理学    |
| 江藤   | 化学      |
| 宇藤   | 化学      |
| 伊藤   | 化学      |
| 安藤   | 化学      |
| 江藤   | 天文学    |
| 宇藤   | 天文学    |
| 伊藤   | 天文学    |
| 安藤   | 天文学    |
+--------+-----------+
16 rows in set (0.00 sec)

+--------+-----------+

| name | name |

+--------+-----------+

| 江藤 | 数学 |

| 宇藤 | 数学 |

| 伊藤 | 数学 |

| 安藤 | 数学 |

| 江藤 | 物理学 |

| 宇藤 | 物理学 |

| 伊藤 | 物理学 |

| 安藤 | 物理学 |

| 江藤 | 化学 |

| 宇藤 | 化学 |

| 伊藤 | 化学 |

| 安藤 | 化学 |

| 江藤 | 天文学 |

| 宇藤 | 天文学 |

| 伊藤 | 天文学 |

| 安藤 | 天文学 |

+--------+-----------+

16 rows in set (0.00 sec)

結合テーブル

studentsテーブルとcoursesテーブルの結合テーブルを準備する。各学生の履修学科に関するデータ。

CREATE TABLE student_courses (
  student_id INT,
  course_id INT
);

INSERT INTO student_courses VALUES
  (1, 10),
  (1, 11),
  (3, 10),
  (3, 11),
  (3, 13),
  (4, 11),
  (4, 13);

CREATE TABLE student_courses (

student_id INT,

course_id INT

);

INSERT INTO student_courses VALUES

(1, 10),

(1, 11),

(3, 10),

(3, 11),

(3, 13),

(4, 11),

(4, 13);

クエリーの実行結果、

mysql> SELECT * FROM student_courses;
+------------+-----------+
| student_id | course_id |
+------------+-----------+
|          1 |        10 |
|          1 |        11 |
|          3 |        10 |
|          3 |        11 |
|          3 |        13 |
|          4 |        11 |
|          4 |        13 |
+------------+-----------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM student_courses;

+------------+-----------+

| student_id | course_id |

+------------+-----------+

| 1 | 10 |

| 1 | 11 |

| 3 | 10 |

| 3 | 11 |

| 3 | 13 |

| 4 | 11 |

| 4 | 13 |

+------------+-----------+

7 rows in set (0.00 sec)

INNER JOIN

結合テーブルに基づいて、2つのテーブルの両方に存在するデータを取り出す。studentsとcoursesの積集合に相当する。INNERは書かなくても内部結合になる。

SELECT
  カラム1,
  カラム2,
  .....
FROM テーブル1 [AS エイリアス1]
[INNER] JOIN テーブル2 [AS エイリアス2]
  ON [テーブル1].キー1 = [テーブル2].キー2;

SELECT

カラム1,

カラム2,

.....

FROM テーブル1 [AS エイリアス1]

[INNER] JOIN テーブル2 [AS エイリアス2]

ON [テーブル1].キー1 = [テーブル2].キー2;

student_coursesテーブルとstudentsテーブルを結合するクエリーの例。結合の結果、studentsテーブルから学生名を得られる。

SELECT
  stcs.student_id,
  st.name,
  stcs.course_id
FROM student_courses AS stcs
JOIN students AS st
ON stcs.student_id = st.id;

SELECT

stcs.student_id,

st.name,

stcs.course_id

FROM student_courses AS stcs

JOIN students AS st

ON stcs.student_id = st.id;

実行結果。

+------------+--------+-----------+
| student_id | name   | course_id |
+------------+--------+-----------+
|          1 | 安藤   |        10 |
|          1 | 安藤   |        11 |
|          3 | 宇藤   |        10 |
|          3 | 宇藤   |        11 |
|          3 | 宇藤   |        13 |
|          4 | 江藤   |        11 |
|          4 | 江藤   |        13 |
+------------+--------+-----------+
7 rows in set (0.00 sec)

+------------+--------+-----------+

| student_id | name | course_id |

+------------+--------+-----------+

| 1 | 安藤 | 10 |

| 1 | 安藤 | 11 |

| 3 | 宇藤 | 10 |

| 3 | 宇藤 | 11 |

| 3 | 宇藤 | 13 |

| 4 | 江藤 | 11 |

| 4 | 江藤 | 13 |

+------------+--------+-----------+

7 rows in set (0.00 sec)

3つのテーブルの場合、JOIN ... ON句を並べればよい。以下の例ではcoursesテーブルも結合して、学科名も得ている。

SELECT
  stcs.student_id,
  st.name AS student_name,
  stcs.course_id,
  cs.name AS course_name
FROM student_courses AS stcs
JOIN students AS st
  ON stcs.student_id = st.id
JOIN courses AS cs
  ON stcs.course_id = cs.id;

SELECT

stcs.student_id,

st.name AS student_name,

stcs.course_id,

cs.name AS course_name

FROM student_courses AS stcs

JOIN students AS st

ON stcs.student_id = st.id

JOIN courses AS cs

ON stcs.course_id = cs.id;

実行結果。student_coursesテーブルは学生IDと学科IDしか持っていないが、studentsテーブル、coursesテーブルと結合することで学生名と学科名を使えるようになる。

+------------+--------------+-----------+-------------+
| student_id | student_name | course_id | course_name |
+------------+--------------+-----------+-------------+
|          3 | 宇藤         |        10 | 数学        |
|          1 | 安藤         |        10 | 数学        |
|          4 | 江藤         |        11 | 物理学      |
|          3 | 宇藤         |        11 | 物理学      |
|          1 | 安藤         |        11 | 物理学      |
|          4 | 江藤         |        13 | 天文学      |
|          3 | 宇藤         |        13 | 天文学      |
+------------+--------------+-----------+-------------+
7 rows in set (0.00 sec)

+------------+--------------+-----------+-------------+

+------------+--------------+-----------+-------------+

| 3 | 宇藤 | 10 | 数学 |

| 1 | 安藤 | 10 | 数学 |

| 4 | 江藤 | 11 | 物理学 |

| 3 | 宇藤 | 11 | 物理学 |

| 1 | 安藤 | 11 | 物理学 |

| 4 | 江藤 | 13 | 天文学 |

| 3 | 宇藤 | 13 | 天文学 |

+------------+--------------+-----------+-------------+

7 rows in set (0.00 sec)

LEFT OUTER JOIN

LEFT OUTER JOINは、先に指定されたテーブル(左テーブル)の全レコードに対して、JOINで指定したレコードを対応させる。左テーブルに対応するレコードがないときはNULLになる。LEFT JOINと書いても同じ。

以下のクエリーは、studentsの全学生データに対してstudent_coursesの履修科目データを左結合させる。studentsには登録されているがstudent_coursesにない学生(学科を履修していない学生)についてはNULLになる。

SELECT
  st.id AS student_id,
  st.name,
  stcs.course_id
FROM students AS st
LEFT JOIN student_courses AS stcs
  ON st.id = stcs.student_id;

SELECT

st.id AS student_id,

st.name,

stcs.course_id

FROM students AS st

LEFT JOIN student_courses AS stcs

ON st.id = stcs.student_id;

実行結果は以下の通りで、学生IDの伊藤君はstudent_coursesテーブルに存在しないので、course_idがNULLになっている。

+------------+--------+-----------+
| student_id | name   | course_id |
+------------+--------+-----------+
|          1 | 安藤   |        11 |
|          1 | 安藤   |        10 |
|          2 | 伊藤   |      NULL |
|          3 | 宇藤   |        13 |
|          3 | 宇藤   |        11 |
|          3 | 宇藤   |        10 |
|          4 | 江藤   |        13 |
|          4 | 江藤   |        11 |
+------------+--------+-----------+
8 rows in set (0.00 sec)

+------------+--------+-----------+

| student_id | name | course_id |

+------------+--------+-----------+

| 1 | 安藤 | 11 |

| 1 | 安藤 | 10 |

| 2 | 伊藤 | NULL |

| 3 | 宇藤 | 13 |

| 3 | 宇藤 | 11 |

| 3 | 宇藤 | 10 |

| 4 | 江藤 | 13 |

| 4 | 江藤 | 11 |

+------------+--------+-----------+

8 rows in set (0.00 sec)

学科名も欲しい場合は、coursesテーブルを左結合させる。

SELECT
  st.id AS student_id,
  st.name AS student_name,
  cs.id AS course_id,
  cs.name AS course_name
FROM students AS st
LEFT JOIN student_courses AS stcs
  ON st.id = stcs.student_id
LEFT JOIN courses AS cs
  ON stcs.course_id = cs.id;

SELECT

st.id AS student_id,

st.name AS student_name,

cs.id AS course_id,

cs.name AS course_name

FROM students AS st

LEFT JOIN student_courses AS stcs

ON st.id = stcs.student_id

LEFT JOIN courses AS cs

ON stcs.course_id = cs.id;

実行結果。学科を履修していない伊藤君のデータは、course_nameについてもNULLになる。

+------------+--------------+-----------+-------------+
| student_id | student_name | course_id | course_name |
+------------+--------------+-----------+-------------+
|          1 | 安藤         |        11 | 物理学      |
|          1 | 安藤         |        10 | 数学        |
|          2 | 伊藤         |      NULL | NULL        |
|          3 | 宇藤         |        13 | 天文学      |
|          3 | 宇藤         |        11 | 物理学      |
|          3 | 宇藤         |        10 | 数学        |
|          4 | 江藤         |        13 | 天文学      |
|          4 | 江藤         |        11 | 物理学      |
+------------+--------------+-----------+-------------+
8 rows in set (0.00 sec)

+------------+--------------+-----------+-------------+

+------------+--------------+-----------+-------------+

| 1 | 安藤 | 11 | 物理学 |

| 1 | 安藤 | 10 | 数学 |

| 2 | 伊藤 | NULL | NULL |

| 3 | 宇藤 | 13 | 天文学 |

| 3 | 宇藤 | 11 | 物理学 |

| 3 | 宇藤 | 10 | 数学 |

| 4 | 江藤 | 13 | 天文学 |

| 4 | 江藤 | 11 | 物理学 |

+------------+--------------+-----------+-------------+

8 rows in set (0.00 sec)

RIGHT OUTER JOIN

RIGHT JOINは後から指定されたテーブルの全データに対して、先に指定されたテーブルを結合させる。先に指定されたテーブルに対応するレコードがない場合にはNULLになる。RIGHT JOINと書いても同じ。

以下は、student_coursesテーブルのデータにcoursesテーブルのデータを右結合させている。どの学生にも履修されていない学科がある場合はNULLになる。

SELECT
  stcs.student_id,
  cs.id AS course_id,
  cs.name AS course_name
FROM student_courses AS stcs
RIGHT JOIN courses as cs
  ON stcs.course_id = cs.id;

SELECT

stcs.student_id,

cs.id AS course_id,

cs.name AS course_name

FROM student_courses AS stcs

RIGHT JOIN courses as cs

ON stcs.course_id = cs.id;

実行結果。化学はどの学生にも履修されていないのでNULLになっている。

+------------+-----------+-------------+
| student_id | course_id | course_name |
+------------+-----------+-------------+
|          3 |        10 | 数学        |
|          1 |        10 | 数学        |
|          4 |        11 | 物理学      |
|          3 |        11 | 物理学      |
|          1 |        11 | 物理学      |
|       NULL |        12 | 化学        |
|          4 |        13 | 天文学      |
|          3 |        13 | 天文学      |
+------------+-----------+-------------+
8 rows in set (0.00 sec)

+------------+-----------+-------------+

| student_id | course_id | course_name |

+------------+-----------+-------------+

| 3 | 10 | 数学 |

| 1 | 10 | 数学 |

| 4 | 11 | 物理学 |

| 3 | 11 | 物理学 |

| 1 | 11 | 物理学 |

| NULL | 12 | 化学 |

| 4 | 13 | 天文学 |

| 3 | 13 | 天文学 |

+------------+-----------+-------------+

8 rows in set (0.00 sec)

3つ以上のテーブルでは、最も右側のテーブルをRIGHT JOINを連ねる。最後以外のJOINはRIGHTがなくても同じ結果になる。

SELECT
  st.id AS student_id,
  st.name AS student_name,
  cs.id AS course_id,
  cs.name AS course_name
FROM student_courses AS stcs
RIGHT JOIN students AS st
  ON stcs.student_id = st.id
RIGHT JOIN courses as cs
  ON stcs.course_id = cs.id;

SELECT

st.id AS student_id,

st.name AS student_name,

cs.id AS course_id,

cs.name AS course_name

FROM student_courses AS stcs

RIGHT JOIN students AS st

ON stcs.student_id = st.id

RIGHT JOIN courses as cs

ON stcs.course_id = cs.id;

実行結果。履修されていない化学の学生IDと学生名がNULLになっている。

+------------+--------------+-----------+-------------+
| student_id | student_name | course_id | course_name |
+------------+--------------+-----------+-------------+
|          3 | 宇藤         |        10 | 数学        |
|          1 | 安藤         |        10 | 数学        |
|          4 | 江藤         |        11 | 物理学      |
|          3 | 宇藤         |        11 | 物理学      |
|          1 | 安藤         |        11 | 物理学      |
|       NULL | NULL         |        12 | 化学        |
|          4 | 江藤         |        13 | 天文学      |
|          3 | 宇藤         |        13 | 天文学      |
+------------+--------------+-----------+-------------+
8 rows in set (0.00 sec)

+------------+--------------+-----------+-------------+

+------------+--------------+-----------+-------------+

| 3 | 宇藤 | 10 | 数学 |

| 1 | 安藤 | 10 | 数学 |

| 4 | 江藤 | 11 | 物理学 |

| 3 | 宇藤 | 11 | 物理学 |

| 1 | 安藤 | 11 | 物理学 |

| NULL | NULL | 12 | 化学 |

| 4 | 江藤 | 13 | 天文学 |

| 3 | 宇藤 | 13 | 天文学 |

+------------+--------------+-----------+-------------+

8 rows in set (0.00 sec)

RIGHT JOINのテーブルを入れ替えることで、LEFT JOINで同じ結果を得られる。履修科目に関する上の例をLEFT JOINで書きなおすと以下の通り。

SELECT
  st.id AS student_id,
  st.name AS student_name,
  cs.id AS course_id,
  cs.name AS course_name
FROM courses AS cs
LEFT JOIN student_courses AS stcs
  ON stcs.course_id = cs.id
LEFT JOIN students AS st
  ON stcs.student_id = st.id;

SELECT

st.id AS student_id,

st.name AS student_name,

cs.id AS course_id,

cs.name AS course_name

FROM courses AS cs

LEFT JOIN student_courses AS stcs

ON stcs.course_id = cs.id

LEFT JOIN students AS st

ON stcs.student_id = st.id;

実行結果は同じ。

+------------+--------------+-----------+-------------+
| student_id | student_name | course_id | course_name |
+------------+--------------+-----------+-------------+
|          3 | 宇藤         |        10 | 数学        |
|          1 | 安藤         |        10 | 数学        |
|          4 | 江藤         |        11 | 物理学      |
|          3 | 宇藤         |        11 | 物理学      |
|          1 | 安藤         |        11 | 物理学      |
|       NULL | NULL         |        12 | 化学        |
|          4 | 江藤         |        13 | 天文学      |
|          3 | 宇藤         |        13 | 天文学      |
+------------+--------------+-----------+-------------+
8 rows in set (0.00 sec)

+------------+--------------+-----------+-------------+

+------------+--------------+-----------+-------------+

| 3 | 宇藤 | 10 | 数学 |

| 1 | 安藤 | 10 | 数学 |

| 4 | 江藤 | 11 | 物理学 |

| 3 | 宇藤 | 11 | 物理学 |

| 1 | 安藤 | 11 | 物理学 |

| NULL | NULL | 12 | 化学 |

| 4 | 江藤 | 13 | 天文学 |

| 3 | 宇藤 | 13 | 天文学 |

+------------+--------------+-----------+-------------+

8 rows in set (0.00 sec)

FULL OUTER JOIN

FULL OUTER JOINは指定されたテーブルの全データを含める。いずれかのデータが存在しない場合はNULLとなる。テーブルのデータ群の和集合に相当する。

MySQLにはFULL JOINが定義されていないので、同等の機能はUNIONで実現する。

2つのテーブルの場合にはLEFT JOINとRIGHT JOINの結果をUNION
結合テーブルがある場合には、それぞれのLEFT JOINの結果をUNION
UNIONの際に、各SELECTのカラムを同じにしておく

以下は、students、coursesに対するstudent_coursesのLEFT JOINの結果をUNIONで結合している。科目を履修していない学生は学科がNULLとなり、履修されていない科目が学生がNULLとなる。

SELECT
  st.id AS student_id,
  st.name AS student_name,
  cs.id AS course_id,
  cs.name AS course_name
FROM students AS st
LEFT JOIN student_courses AS stcs
  ON st.id = stcs.student_id
LEFT JOIN courses AS cs
  ON stcs.course_id = cs.id
UNION
SELECT
  st.id AS student_id,
  st.name AS student_name,
  cs.id AS course_id,
  cs.name AS course_name
FROM courses AS cs
LEFT JOIN student_courses AS stcs
  ON stcs.course_id = cs.id
LEFT JOIN students AS st
  ON stcs.student_id = st.id;

SELECT

st.id AS student_id,

st.name AS student_name,

cs.id AS course_id,

cs.name AS course_name

FROM students AS st

LEFT JOIN student_courses AS stcs

ON st.id = stcs.student_id

LEFT JOIN courses AS cs

ON stcs.course_id = cs.id

UNION

SELECT

st.id AS student_id,

st.name AS student_name,

cs.id AS course_id,

cs.name AS course_name

FROM courses AS cs

LEFT JOIN student_courses AS stcs

ON stcs.course_id = cs.id

LEFT JOIN students AS st

ON stcs.student_id = st.id;

実行結果。学科を履修していない伊藤君の学科欄はNULLになり、だれにも履修されていない化学は学生欄がNULLになっている。

+------------+--------------+-----------+-------------+
| student_id | student_name | course_id | course_name |
+------------+--------------+-----------+-------------+
|          1 | 安藤         |        11 | 物理学      |
|          1 | 安藤         |        10 | 数学        |
|          2 | 伊藤         |      NULL | NULL        |
|          3 | 宇藤         |        13 | 天文学      |
|          3 | 宇藤         |        11 | 物理学      |
|          3 | 宇藤         |        10 | 数学        |
|          4 | 江藤         |        13 | 天文学      |
|          4 | 江藤         |        11 | 物理学      |
|       NULL | NULL         |        12 | 化学        |
+------------+--------------+-----------+-------------+
9 rows in set (0.00 sec)

+------------+--------------+-----------+-------------+

+------------+--------------+-----------+-------------+

| 1 | 安藤 | 11 | 物理学 |

| 1 | 安藤 | 10 | 数学 |

| 2 | 伊藤 | NULL | NULL |

| 3 | 宇藤 | 13 | 天文学 |

| 3 | 宇藤 | 11 | 物理学 |

| 3 | 宇藤 | 10 | 数学 |

| 4 | 江藤 | 13 | 天文学 |

| 4 | 江藤 | 11 | 物理学 |

| NULL | NULL | 12 | 化学 |

+------------+--------------+-----------+-------------+

9 rows in set (0.00 sec)

カラム名の指定によって結果が違う

LEFT OUTER JOINのところで、学生IDと学科IDをstudents.id、couses.idのようにそれぞれのテーブルから指定していた。

一方で、同じIDをstudent_courseのstudent_id、couse_idで指定することもできる。こちらで指定すれば、識別のためのエイリアスを定義しなくていいので便利そうだ。

SELECT
  stcs.student_id,
  st.name AS student_name,
  stcs.course_id,
  cs.name AS course_name
FROM courses AS cs
LEFT JOIN student_courses AS stcs
  ON stcs.course_id = cs.id
LEFT JOIN students AS st
  ON stcs.student_id = st.id;

SELECT

stcs.student_id,

st.name AS student_name,

stcs.course_id,

cs.name AS course_name

FROM courses AS cs

LEFT JOIN student_courses AS stcs

ON stcs.course_id = cs.id

LEFT JOIN students AS st

ON stcs.student_id = st.id;

しかしこれらを使うと、OUTER JOINの際に存在しないIDとして扱われてNULLになってしまう。

上のクエリーを実行した結果が以下で、化学を履修している学生がいないので、student_coursesに該当するデータがなく、student_idとcourse_idがNULLになっている。

+------------+--------------+-----------+-------------+
| student_id | student_name | course_id | course_name |
+------------+--------------+-----------+-------------+
|          3 | 宇藤         |        10 | 数学        |
|          1 | 安藤         |        10 | 数学        |
|          4 | 江藤         |        11 | 物理学      |
|          3 | 宇藤         |        11 | 物理学      |
|          1 | 安藤         |        11 | 物理学      |
|       NULL | NULL         |      NULL | 化学        |
|          4 | 江藤         |        13 | 天文学      |
|          3 | 宇藤         |        13 | 天文学      |
+------------+--------------+-----------+-------------+
8 rows in set (0.00 sec)

+------------+--------------+-----------+-------------+

+------------+--------------+-----------+-------------+

| 3 | 宇藤 | 10 | 数学 |

| 1 | 安藤 | 10 | 数学 |

| 4 | 江藤 | 11 | 物理学 |

| 3 | 宇藤 | 11 | 物理学 |

| 1 | 安藤 | 11 | 物理学 |

| 4 | 江藤 | 13 | 天文学 |

| 3 | 宇藤 | 13 | 天文学 |

+------------+--------------+-----------+-------------+

8 rows in set (0.00 sec)

これらを表示させたいときには、LEFT OUTER JOINの例のように、左結合される側(全データが使われる側)のテーブルでカラムを指定するとよい。

MySQL – 外部キー制約

2021-08-21 / tau / コメントする

外部キーの設定

テーブル構成

学生の履修科目に関する簡単なデータベースを考える。

studentsテーブルは学生に関するデータを保存
- 学生ID(主キー)
- 学生の名前
student_coursesテーブルは学生の履修科目に関するデータを保存
- 履修科目ID(主キー)
- 学生ID
- 科目名

学生テーブル

以下のクエリーでテーブルを作成し、学生データを登録。

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(10)
);

INSERT INTO students (id, name) VALUES
  (1, '安藤'),
  (2, '伊藤'),
  (3, '宇藤');

CREATE TABLE students (

id INT PRIMARY KEY,

name VARCHAR(10)

);

INSERT INTO students (id, name) VALUES

(1, '安藤'),

(2, '伊藤'),

(3, '宇藤');

登録結果の確認。

mysql> SELECT * FROM students;
+----+--------+
| id | name   |
+----+--------+
|  1 | 安藤   |
|  2 | 伊藤   |
|  3 | 宇藤   |
+----+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM students;

+----+--------+

| id | name |

+----+--------+

| 1 | 安藤 |

| 2 | 伊藤 |

| 3 | 宇藤 |

+----+--------+

3 rows in set (0.00 sec)

履修科目テーブル～外部キーの設定

以下のクエリーで履修科目テーブルを作成。student_idを外部キーとして、studentsテーブルのid(学生ID)を参照している。

外部キーを設定するカラムについて、以下のように定義する。

FOREIGN_KEY (参照するキー) REFERENCES 参照されるテーブル(参照されるキー)

CREATE TABLE student_courses (
  id INT PRIMARY KEY,
  student_id INT,
  FOREIGN KEY (student_id) REFERENCES students(id),
  course_name VARCHAR(10)
);

CREATE TABLE student_courses (

id INT PRIMARY KEY,

student_id INT,

FOREIGN KEY (student_id) REFERENCES students(id),

course_name VARCHAR(10)

);

テーブル構造を確認すると、student_idのKey欄がMULとなる。このMULについてはぴったりの情報が得られなかったが、以下のようなことらしい。

外部参照するキーにはインデックスが設定される
PRIMARY KEYやINIQUEが設定されていないインデックスは同じ値を取り得る
なのでmultiple keyでMULと表示される

mysql> DESCRIBE student_courses;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int         | NO   | PRI | NULL    |       |
| student_id  | int         | YES  | MUL | NULL    |       |
| course_name | varchar(10) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DESCRIBE student_courses;

+-------------+-------------+------+-----+---------+-------+

+-------------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

| student_id | int | YES | MUL | NULL | |

+-------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

外部キー制約の確認

正常な登録

新しいデータをstudent_coursesテーブルに登録する。student_idとして登録済みのstudents.id = 1の学生IDを指定する。

INSERT INTO student_courses (id, student_id, course_name) VALUES (1, 1, '数学');

1	INSERT INTO student_courses (id, student_id, course_name) VALUES (1, 1, '数学');

正常に登録されて、students_coursesテーブルは以下のようになる。

mysql> INSERT INTO student_courses (id, student_id, course_name) VALUES (1, 1, '数学');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student_courses;
+------+------------+-------------+
| id   | student_id | course_name |
+------+------------+-------------+
|    1 |          1 | 数学        |
+------+------------+-------------+
1 row in set (0.00 sec)

mysql> INSERT INTO student_courses (id, student_id, course_name) VALUES (1, 1, '数学');

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student_courses;

+------+------------+-------------+

| id | student_id | course_name |

+------+------------+-------------+

| 1 | 1 | 数学 |

+------+------------+-------------+

1 row in set (0.00 sec)

存在しないデータの登録はエラー

student_idに、studentsテーブルでは登録されていない値を指定する。

INSERT INTO student_courses (id, student_id, course_name) VALUES (2, 4, '数学');

1	INSERT INTO student_courses (id, student_id, course_name) VALUES (2, 4, '数学');

外部キー制約でエラーとなる。

mysql> INSERT INTO student_courses (id, student_id, course_name) VALUES (2, 4, '数学');
ERROR 1452 (23000): Cannot add or update a child row:
 a foreign key constraint fails (`testdb`.`student_courses`,
 CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`))
mysql>

mysql> INSERT INTO student_courses (id, student_id, course_name) VALUES (2, 4, '数学');

ERROR 1452 (23000): Cannot add or update a child row:

a foreign key constraint fails (`testdb`.`student_courses`,

CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`))

mysql>

参照されている親のデータは消せない

student_coursesテーブルのデータから参照されている、studentsテーブルのid=1のデータを削除しようとするとエラーになる。

mysql> DELETE FROM students WHERE id=1;
ERROR 1451 (23000): Cannot delete or update a parent row:
 a foreign key constraint fails (`testdb`.`student_courses`,
 CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`))

mysql> DELETE FROM students WHERE id=1;

ERROR 1451 (23000): Cannot delete or update a parent row:

a foreign key constraint fails (`testdb`.`student_courses`,

CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`))

参照されていない親のデータは削除できる

mysql> DELETE FROM students WHERE id=2;
Query OK, 1 row affected (0.01 sec)

1 2	mysql> DELETE FROM students WHERE id=2; Query OK, 1 row affected (0.01 sec)

student_coursesのデータから参照されていない、studentsテーブルのid=2のデータは削除できる。

CREATE TABLE student_courses (
  id INT,
  student_id INT,
  FOREIGN KEY fk_student_id(student_id) REFERENCES students(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  course_name VARCHAR(10)
);

CREATE TABLE student_courses (

id INT,

student_id INT,

FOREIGN KEY fk_student_id(student_id) REFERENCES students(id)

ON DELETE CASCADE

ON UPDATE CASCADE,

course_name VARCHAR(10)

);

不整合時の挙動～RESTRICTやCASCADEなど

FOREIGN KEY指定時に、参照先のデータの削除時と変更時の挙動を指定できる。

ON DELETE 挙動指定 ON UPDATE 挙動指定

挙動指定には以下の4つがあり、指定しない場合はRESTRICTとなる。

RESTRICT: DELETE, UPDATEともエラーになる。
CASCADE: DELETEでは参照元のデータも削除され、UPDATEでは参照先の変更が参照元のデータに反映される。
SET NULL: DELETE, UPDATEともNULLに置き換わる。
NO ACTION: RESTRICTと同じ挙動。

外部キー制約をCASCADEに変更する

CREATE TABLEの確認

外部キー制約設定後のテーブル定義を以下のコマンドで確認してみる。

SHOW CREATE TABLE テーブル名\G;

最初に実行したクエリーの内容に即している
CONSTRAINT `外部制約名`が追加されている

mysql> SHOW CREATE TABLE student_courses\G
*************************** 1. row ***************************
       Table: student_courses
Create Table: CREATE TABLE `student_courses` (
  `id` int NOT NULL,
  `student_id` int DEFAULT NULL,
  `course_name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE student_courses\G

*************************** 1. row ***************************

Table: student_courses

Create Table: CREATE TABLE `student_courses` (

`id` int NOT NULL,

`student_id` int DEFAULT NULL,

`course_name` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `student_id` (`student_id`),

CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

挙動の変更

外部キー制約の挙動をRESTRICTからCASCADEに変更する。外部キーの操作はALTER TABLEで行うが、一度に変更できないので一旦外部キーを削除し、新たな条件で外部キーを追加する。

外部キーの削除

外部キー削除のコマンドは以下の通り。

ALTER TABLE 参照元テーブル DROP FOREIGN KEY 外部制約名;

外部制約名は、先のSHOW CREATE TABLEで確認した内容で指定する。

mysql> ALTER TABLE student_courses DROP FOREIGN KEY student_courses_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE student_courses DROP FOREIGN KEY student_courses_ibfk_1;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

外部キー削除後もインデックスは残っている。

mysql> DESCRIBE student_courses;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int         | NO   | PRI | NULL    |       |
| student_id  | int         | YES  | MUL | NULL    |       |
| course_name | varchar(10) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> DESCRIBE student_courses;

+-------------+-------------+------+-----+---------+-------+

+-------------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

| student_id | int | YES | MUL | NULL | |

+-------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

外部キー削除後のCREATE TABLEを確認すると、student_idにインデックスが設定されている。

mysql> SHOW CREATE TABLE student_courses\G
*************************** 1. row ***************************
       Table: student_courses
Create Table: CREATE TABLE `student_courses` (
  `id` int NOT NULL,
  `student_id` int DEFAULT NULL,
  `course_name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE student_courses\G

*************************** 1. row ***************************

Table: student_courses

Create Table: CREATE TABLE `student_courses` (

`id` int NOT NULL,

`student_id` int DEFAULT NULL,

`course_name` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `student_id` (`student_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

外部キーの追加

以下のクエリーで削除・更新時の挙動を指定して外部キーを追加。

ALTER TABLE student_courses
ADD FOREIGN KEY (student_id) REFERENCES students(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE student_courses

ADD FOREIGN KEY (student_id) REFERENCES students(id)

ON DELETE CASCADE

ON UPDATE CASCADE;

クエリー実行。

mysql> ALTER TABLE student_courses
    -> ADD FOREIGN KEY (student_id) REFERENCES students(id)
    ->   ON DELETE CASCADE
    ->   ON UPDATE CASCADE;
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE student_courses

-> ADD FOREIGN KEY (student_id) REFERENCES students(id)

-> ON DELETE CASCADE

-> ON UPDATE CASCADE;

Query OK, 5 rows affected (0.03 sec)

Records: 5 Duplicates: 0 Warnings: 0

CREATE TABLE確認。

mysql> SHOW CREATE TABLE student_courses\G
*************************** 1. row ***************************
       Table: student_courses
Create Table: CREATE TABLE `student_courses` (
  `id` int NOT NULL,
  `student_id` int DEFAULT NULL,
  `course_name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE student_courses\G

*************************** 1. row ***************************

Table: student_courses

Create Table: CREATE TABLE `student_courses` (

`id` int NOT NULL,

`student_id` int DEFAULT NULL,

`course_name` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `student_id` (`student_id`),

CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

CASCADEの挙動

ON UPDATE CASCADE

参照先、親テーブルstudentsのデータのid=3を9に変更する。RESTRICTではエラーになったが、CASCADEの場合は変更が通る。

mysql> UPDATE students SET id = 9 WHERE id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE students SET id = 9 WHERE id = 3;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

student_coursesのstudent_idも変更されている。

+----+------------+--------+-------------+
| id | student_id | name   | course_name |
+----+------------+--------+-------------+
|  1 |          1 | 安藤   | 数学        |
|  2 |          1 | 安藤   | 物理学      |
|  3 |          2 | 伊藤   | 数学        |
|  4 |          9 | 宇藤   | 物理学      |
|  5 |          9 | 宇藤   | 化学        |
+----+------------+--------+-------------+
5 rows in set (0.00 sec)

+----+------------+--------+-------------+

+----+------------+--------+-------------+

| 1 | 1 | 安藤 | 数学 |

| 2 | 1 | 安藤 | 物理学 |

| 3 | 2 | 伊藤 | 数学 |

| 4 | 9 | 宇藤 | 物理学 |

| 5 | 9 | 宇藤 | 化学 |

+----+------------+--------+-------------+

5 rows in set (0.00 sec)

ON DELETE CASCADE

参照先、親テーブルのid=9のレコードを削除する。RESTRICTではエラーになったが、CASCADEの場合は削除される。

mysql> DELETE FROM students WHERE id = 9;
Query OK, 1 row affected (0.00 sec)

1 2	mysql> DELETE FROM students WHERE id = 9; Query OK, 1 row affected (0.00 sec)

student_coursesのstudent_id=9のレコードも削除されている。

+----+------------+--------+-------------+
| id | student_id | name   | course_name |
+----+------------+--------+-------------+
|  1 |          1 | 安藤   | 数学        |
|  2 |          1 | 安藤   | 物理学      |
|  3 |          2 | 伊藤   | 数学        |
+----+------------+--------+-------------+
3 rows in set (0.01 sec)

+----+------------+--------+-------------+

+----+------------+--------+-------------+

| 1 | 1 | 安藤 | 数学 |

| 2 | 1 | 安藤 | 物理学 |

| 3 | 2 | 伊藤 | 数学 |

+----+------------+--------+-------------+

3 rows in set (0.01 sec)

外部キー制約の追加・削除

外部キーの追加は、ALTER TABLE ... ADD FOREIGIN KEYで行う。被参照キーにインデックスが設定されている必要がある。

ALTER TABLE 参照テーブル
ADD FOREIGN KEY (参照キー)
REFERENCES 被参照テーブル(被参照キー);

ALTER TABLE 参照テーブル

ADD FOREIGN KEY (参照キー)

REFERENCES 被参照テーブル(被参照キー);

外部キーの削除はALTER TABLE ... DROP FOREIGN KEYで行う。制約名はSHOW CREATE TABLEで確認できる。

ALTER TABLE 参照テーブル DROP FOREIGN KEY 制約名;

1	ALTER TABLE 参照テーブル DROP FOREIGN KEY 制約名;

MySQL – テーブルの作成クエリーを表示する

2021-08-21 / tau / コメントする

作成済みのテーブルの作成クエリーを表示させることができる。

SHOW CREATE TABLE テーブル名\G

列は1列だが幅が広いので\Gで縦表示している。

mysql> show create table student_courses\G
*************************** 1. row ***************************
       Table: student_courses
Create Table: CREATE TABLE `student_courses` (
  `id` int NOT NULL,
  `student_id` int DEFAULT NULL,
  `course_name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> show create table student_courses\G

*************************** 1. row ***************************

Table: student_courses

Create Table: CREATE TABLE `student_courses` (

`id` int NOT NULL,

`student_id` int DEFAULT NULL,

`course_name` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `student_id` (`student_id`),

CONSTRAINT `student_courses_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

MySQL – レコード登録・更新のタイムスタンプ

2021-08-20 / tau / コメントする

レコードを登録・更新した場合のタイムスタンプを保存するためのテーブル設定。

日時の型はDATETIMEで設定
作成時の初期値を作成時点の日時とするにはDEFAULTを指定
- DEFAULT CURRENT_TIMESTAMP
更新時に更新時点の日時で書き換える場合はON UPDATEを指定
- ON UPDATE CURRENT_TIMESTAMP

テーブル作成のSQL例。

CREATE TABLE test (
  number INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE test (

number INT,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

SQL実行結果。

mysql> CREATE TABLE test (
    ->   number INT,
    ->   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    ->   updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESCRIBE test;
+------------+----------+------+-----+-------------------+-----------------------------------------------+
| Field      | Type     | Null | Key | Default           | Extra                                         |
+------------+----------+------+-----+-------------------+-----------------------------------------------+
| number     | int      | YES  |     | NULL              |                                               |
| created_at | datetime | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| updated_at | datetime | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------+----------+------+-----+-------------------+-----------------------------------------------+
3 rows in set (0.00 sec)

mysql> CREATE TABLE test (

-> number INT,

-> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

-> updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> DESCRIBE test;

+------------+----------+------+-----+-------------------+-----------------------------------------------+

+------------+----------+------+-----+-------------------+-----------------------------------------------+

| number | int | YES | | NULL | |

+------------+----------+------+-----+-------------------+-----------------------------------------------+

3 rows in set (0.00 sec)

テストデータ登録。

mysql> INSERT INTO test (number) VALUES (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+--------+---------------------+---------------------+
| number | created_at          | updated_at          |
+--------+---------------------+---------------------+
|      1 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 |
|      2 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 |
|      3 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 |
+--------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO test (number) VALUES (1), (2), (3);

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM test;

+--------+---------------------+---------------------+

| number | created_at | updated_at |

+--------+---------------------+---------------------+

| 1 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 |

| 2 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 |

| 3 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 |

+--------+---------------------+---------------------+

3 rows in set (0.00 sec)

2つ目のデータを変更して、更新日時が変更されていることを確認。

mysql> UPDATE test SET number=9 WHERE number=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM test;
+--------+---------------------+---------------------+
| number | created_at          | updated_at          |
+--------+---------------------+---------------------+
|      1 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 |
|      9 | 2021-08-20 06:41:34 | 2021-08-20 06:43:13 |
|      3 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 |
+--------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> UPDATE test SET number=9 WHERE number=2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM test;

+--------+---------------------+---------------------+

| number | created_at | updated_at |

+--------+---------------------+---------------------+

| 1 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 |

| 9 | 2021-08-20 06:41:34 | 2021-08-20 06:43:13 |

| 3 | 2021-08-20 06:41:34 | 2021-08-20 06:41:34 |

+--------+---------------------+---------------------+

3 rows in set (0.00 sec)