MySQL 5.6 Developer試験対策 9 MySQL アプリケーションの開発 -- のこり
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
MySQLの標準的なドライバを使用するPHP、Java、および.NET開発の主要な特徴、機能およびオプションを識別する
PHP
- 2つ(古いもの含め3つ)ある
- PHPにはコネクションプールはない
- 単に持続的に接続しているだけ
- 【所感】2020/06/06現在、業務で普段遣いしているのはPDO
- LaravelのConnectionの内部で使われている
Java
- たぶんこれ
.NET
- たぶんこれ
MySQlのエラー・メッセージを解釈する
- エラーメッセージ言語を設定できる
mysqld --lc_messages_dir=/usr/share/mysql --lc_messages=fr_FR
- 上記起動オプションで起動したならば:
- fr_FRが
frenchにマッピングされる /usr/share/mysql/frenchでエラーメッセージが検索される
- fr_FRが
サーバーのエラーコードおよびメッセージ
エラー: 1045 SQLSTATE: 28000 (ER_ACCESS_DENIED_ERROR) メッセージ: ユーザー '%s'@'%s' のアクセスは拒否されました (使用パスワード: %s)
こういうやつ
クライアントのエラーコードおよびメッセージ
エラー: 2001 (CR_SOCKET_CREATE_ERROR) メッセージ: UNIX のソケット (%d) を作成できません
こういうやつ
使用可能な診断情報を収集する
mysql> DROP TABLE test.no_such_table; ERROR 1051 (42S02): Unknown table 'test.no_such_table'
- テーブルを使用するため診断領域クリア
- 条件が発生したので移入する
mysql> SHOW WARNINGS; +-------+------+------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------+ | Error | 1051 | Unknown table 'test.no_such_table' | +-------+------+------------------------------------+ 1 row in set (0.00 sec)
- 診断情報抽出
GET DIAGNOSTICS CONDITION 1 @p1 = MYSQL_ERRNO,@p2 = RETURNED_SQLSTATE, @p3 = MESSAGE_TEXT; SELECT @p1 AS MYSQL_ERRNO , @p2 AS RETURNED_SQLSTATE , @p3 AS MESSAGE_TEXT FROM DUAL;
+-------------+-------------------+------------------------------------+ | MYSQL_ERRNO | RETURNED_SQLSTATE | MESSAGE_TEXT | +-------------+-------------------+------------------------------------+ | 1051 | 42S02 | Unknown table 'test.no_such_table' | +-------------+-------------------+------------------------------------+ 1 row in set (0.00 sec)
- テーブル不使用、警告も生成しない場合、診断領域はそのまま
mysql> SET @x = 1; Query OK, 0 rows affected (0.00 sec) mysql> SHOW WARNINGS; +-------+------+------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------+ | Error | 1051 | Unknown table 'test.no_such_table' | +-------+------+------------------------------------+ 1 row in set (0.00 sec)
- テーブルを使用せず、エラー生成する場合
- 診断領域をクリアして移入
mysql> SET @x = @@x; ERROR 1193 (HY000): Unknown system variable 'x' mysql> SHOW WARNINGS; +-------+------+-----------------------------+ | Level | Code | Message | +-------+------+-----------------------------+ | Error | 1193 | Unknown system variable 'x' | +-------+------+-----------------------------+ 1 row in set (0.00 sec)
- 条件番号が1までしかないのに2の抽出を試みてみる
- テーブルを触らず、警告発生
- 診断領域はクリアされず、条件が移入される
mysql> GET DIAGNOSTICS CONDITION 2 @p = MESSAGE_TEXT; Query OK, 0 rows affected, 1 warning (0.00 sec) SHOW WARNINGS; +-------+------+-----------------------------+ | Level | Code | Message | +-------+------+-----------------------------+ | Error | 1193 | Unknown system variable 'x' | | Error | 1758 | Invalid condition number | +-------+------+-----------------------------+ 2 rows in set (0.00 sec)
- 2つめが入ったので今度はちゃんと動く
mysql> GET DIAGNOSTICS CONDITION 2 @p = MESSAGE_TEXT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @p; +--------------------------+ | @p | +--------------------------+ | Invalid condition number | +--------------------------+ 1 row in set (0.00 sec)
- 警告の場合でも、テーブルを触ると診断領域はクリアされる
mysql> DROP TABLE no_such_table;
ERROR 1051 (42S02): Unknown table 'sample.no_such_table'
mysql> SHOW WARNINGS;
+-------+------+--------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------+
| Error | 1051 | Unknown table 'sample.no_such_table' |
+-------+------+--------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE tbl (d timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tbl VALUES ('2039-01-01 00:00:00');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'd' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
診断領域をクリアするか否か
| テーブル触る | テーブル触らない | |
|---|---|---|
| 警告 | クリア | |
| エラー | クリア | クリア |
- CONDITIONの数はNUMBERで取れる
mysql> DROP TABLE no_such_table; ERROR 1051 (42S02): Unknown table 'sample.no_such_table' mysql> GET DIAGNOSTICS CONDITION 3 @p = MESSAGE_TEXT; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GET DIAGNOSTICS CONDITION 3 @p = MESSAGE_TEXT; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GET DIAGNOSTICS CONDITION 3 @p = MESSAGE_TEXT; Query OK, 0 rows affected (0.00 sec) mysql> GET DIAGNOSTICS @n = NUMBER; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @n; +------+ | @n | +------+ | 3 | +------+ 1 row in set (0.00 sec)
- 関連するシステム変数
mysql> SELECT @@max_error_count,@@warning_count,@@error_count,@@sql_notes; +-------------------+-----------------+---------------+-------------+ | @@max_error_count | @@warning_count | @@error_count | @@sql_notes | +-------------------+-----------------+---------------+-------------+ | 64 | 3 | 3 | 1 | +-------------------+-----------------+---------------+-------------+ 1 row in set (0.00 sec)
max_error_countを超えて追加しようとした分は暗黙理に捨てられる- RESIGNALによって追加された場合はつねに診断領域に追加され、ところてん式に古いものが捨てられる
warning_count,error_countは捨てられた分も数える- 例:
max_error_countを2にしてみる
mysql> SET @@max_error_count = 2; Query OK, 0 rows affected (0.01 sec)
- 警告を4つ発生させる
mysql> DROP TABLE no_such_table; ERROR 1051 (42S02): Unknown table 'sample.no_such_table' mysql> GET DIAGNOSTICS CONDITION 3 @p = MESSAGE_TEXT; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GET DIAGNOSTICS CONDITION 3 @p = MESSAGE_TEXT; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GET DIAGNOSTICS CONDITION 3 @p = MESSAGE_TEXT; Query OK, 0 rows affected, 1 warning (0.00 sec)
- NUMBER (診断領域に実際に格納されている件数)は2
- 上限にかかった
mysql> GET DIAGNOSTICS @n = NUMBER; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @n; +------+ | @n | +------+ | 2 | +------+ 1 row in set (0.00 sec)
- いちばん古い
Unknown table 'sample.no_such_table'が残っている = 新しいものが捨てられている
mysql> SHOW WARNINGS; +-------+------+--------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------+ | Error | 1051 | Unknown table 'sample.no_such_table' | | Error | 1758 | Invalid condition number | +-------+------+--------------------------------------+ 2 rows in set (0.00 sec)
- システム変数確認
mysql> SELECT @@max_error_count,@@warning_count,@@error_count,@@sql_notes; +-------------------+-----------------+---------------+-------------+ | @@max_error_count | @@warning_count | @@error_count | @@sql_notes | +-------------------+-----------------+---------------+-------------+ | 2 | 4 | 4 | 1 | +-------------------+-----------------+---------------+-------------+ 1 row in set (0.00 sec)
MySQL 5.6 Developer試験対策 9 MySQL アプリケーションの開発 -- memcached API
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
memcached API
【補】環境構築
こんな感じに環境を用意する
5.6だとすぐ使えなそうだったので5.7にしちゃった
docker-compose.yml
version: "3" services: mysql5.7: build: context: ./image/5.7 environment: - "MYSQL_ROOT_PASSWORD=root" tty: true
Dockerfile
FROM mysql:5.7 ADD ./1_setup_memcacmed.sh /docker-entrypoint-initdb.d
1_setup_memcached.sh
#!/bin/sh echo 'setup' mysql -u root -proot -vvv < /usr/share/mysql/innodb_memcached_config.sql echo 'setup done' echo 'install' mysql -u root -proot -vvv <<EOF INSTALL PLUGIN daemon_memcached soname "libmemcached.so"; SELECT 'OK' FROM DUAL; EOF echo 'install done'
- 起動ログ
...
mysql5.7_1 | 2020-06-06T00:09:22.282706Z 0 [Note] mysqld: ready for connections.
mysql5.7_1 | Version: '5.7.27' socket: '/var/run/mysqld/mysqld.sock' port: 0 MySQL Community Server (GPL)
mysql5.7_1 | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
mysql5.7_1 | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
mysql5.7_1 | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
mysql5.7_1 | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
mysql5.7_1 |
mysql5.7_1 | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/1_setup_memcacmed.sh
mysql5.7_1 | setup
mysql5.7_1 | mysql: [Warning] Using a password on the command line interface can be insecure.
mysql5.7_1 | --------------
mysql5.7_1 | create database innodb_memcache
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | Query OK, 1 row affected (0.00 sec)
mysql5.7_1 |
mysql5.7_1 | --------------
mysql5.7_1 | CREATE TABLE IF NOT EXISTS `cache_policies` (
mysql5.7_1 | `policy_name` VARCHAR(40) PRIMARY KEY,
mysql5.7_1 | `get_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
mysql5.7_1 | NOT NULL ,
mysql5.7_1 | `set_policy` ENUM('innodb_only', 'cache_only','caching','disabled')
mysql5.7_1 | NOT NULL ,
mysql5.7_1 | `delete_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
mysql5.7_1 | NOT NULL,
mysql5.7_1 | `flush_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
mysql5.7_1 | NOT NULL
mysql5.7_1 | ) ENGINE = innodb
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | Query OK, 0 rows affected (0.01 sec)
mysql5.7_1 |
mysql5.7_1 | --------------
mysql5.7_1 | CREATE TABLE IF NOT EXISTS `containers` (
mysql5.7_1 | `name` varchar(50) not null primary key,
mysql5.7_1 | `db_schema` VARCHAR(250) NOT NULL,
mysql5.7_1 | `db_table` VARCHAR(250) NOT NULL,
mysql5.7_1 | `key_columns` VARCHAR(250) NOT NULL,
mysql5.7_1 | `value_columns` VARCHAR(250),
mysql5.7_1 | `flags` VARCHAR(250) NOT NULL DEFAULT "0",
mysql5.7_1 | `cas_column` VARCHAR(250),
mysql5.7_1 | `expire_time_column` VARCHAR(250),
mysql5.7_1 | `unique_idx_name_on_key` VARCHAR(250) NOT NULL
mysql5.7_1 | ) ENGINE = InnoDB
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | Query OK, 0 rows affected (0.02 sec)
mysql5.7_1 |
mysql5.7_1 | --------------
mysql5.7_1 | CREATE TABLE IF NOT EXISTS `config_options` (
mysql5.7_1 | `name` varchar(50) not null primary key,
mysql5.7_1 | `value` varchar(50)) ENGINE = InnoDB
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | Query OK, 0 rows affected (0.01 sec)
mysql5.7_1 |
mysql5.7_1 | --------------
mysql5.7_1 | INSERT INTO containers VALUES ("aaa", "test", "demo_test",
mysql5.7_1 | "c1", "c2", "c3", "c4", "c5", "PRIMARY")
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | Query OK, 1 row affected (0.00 sec)
mysql5.7_1 |
mysql5.7_1 | --------------
mysql5.7_1 | INSERT INTO cache_policies VALUES("cache_policy", "innodb_only",
mysql5.7_1 | "innodb_only", "innodb_only", "innodb_only")
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | Query OK, 1 row affected (0.01 sec)
mysql5.7_1 |
mysql5.7_1 | --------------
mysql5.7_1 | INSERT INTO config_options VALUES("separator", "|")
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | Query OK, 1 row affected (0.00 sec)
mysql5.7_1 |
mysql5.7_1 | --------------
mysql5.7_1 | INSERT INTO config_options VALUES("table_map_delimiter", ".")
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | Query OK, 1 row affected (0.00 sec)
mysql5.7_1 |
mysql5.7_1 | --------------
mysql5.7_1 | CREATE DATABASE IF NOT EXISTS test
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | Query OK, 1 row affected (0.00 sec)
mysql5.7_1 |
mysql5.7_1 | --------------
mysql5.7_1 | CREATE TABLE demo_test (c1 VARCHAR(32),
mysql5.7_1 | c2 VARCHAR(1024),
mysql5.7_1 | c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
mysql5.7_1 | ENGINE = INNODB
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | Query OK, 0 rows affected (0.01 sec)
mysql5.7_1 |
mysql5.7_1 | --------------
mysql5.7_1 | INSERT INTO demo_test VALUES ("AA", "HELLO, HELLO", 8, 0, 0)
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | Query OK, 1 row affected (0.00 sec)
mysql5.7_1 |
mysql5.7_1 | Bye
mysql5.7_1 | setup done
mysql5.7_1 | install
mysql5.7_1 | mysql: [Warning] Using a password on the command line interface can be insecure.
mysql5.7_1 | --------------
mysql5.7_1 | INSTALL PLUGIN daemon_memcached soname "libmemcached.so"
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | InnoDB MEMCACHED: Memcached uses atomic increment
mysql5.7_1 | Query OK, 0 rows affected (0.00 sec)
mysql5.7_1 |
mysql5.7_1 | --------------
mysql5.7_1 | SELECT 'OK' FROM DUAL
mysql5.7_1 | --------------
mysql5.7_1 |
mysql5.7_1 | +----+
mysql5.7_1 | | OK |
mysql5.7_1 | +----+
mysql5.7_1 | | OK |
mysql5.7_1 | +----+
mysql5.7_1 | 1 row in set (0.00 sec)
mysql5.7_1 |
mysql5.7_1 | Bye
mysql5.7_1 | install done
mysql5.7_1 |
...
- やっていること
- コンフィグテーブルとデモテーブルの確認
mysql> SELECT * FROM innodb_memcache.containers\G
*************************** 1. row ***************************
name: aaa
db_schema: test
db_table: demo_test
key_columns: c1
value_columns: c2
flags: c3
cas_column: c4
expire_time_column: c5
unique_idx_name_on_key: PRIMARY
1 row in set (0.00 sec)
mysql> SELECT * FROM test.demo_test;
+----+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+----+--------------+------+------+------+
| AA | HELLO, HELLO | 8 | 0 | 0 |
+----+--------------+------+------+------+
1 row in set (0.00 sec)
innodb_memcache.containersには、どのスキーマのどのテーブルをどのように解釈するかの情報が格納されるtest.demo_testの定義と照らす
mysql> DESC test.demo_test; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | c1 | varchar(32) | NO | PRI | NULL | | | c2 | varchar(1024) | YES | | NULL | | | c3 | int(11) | YES | | NULL | | | c4 | bigint(20) unsigned | YES | | NULL | | | c5 | int(11) | YES | | NULL | | +-------+---------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
key_columns: c1
value_columns: c2
flags: c3
cas_column: c4
expire_time_column: c5
- PK, VARCHARなキーが必要
- 3つの管理カラムが必要
- フラグ
- 連番
- expiration timestamp
動作確認
root@db45fbb2747e:/# telnet localhost 11211 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'.
AAキーによるデータ取得
get AA VALUE AA 8 12 HELLO, HELLO END
BBキーによるデータ挿入
set BB 10 0 16 GOODBYE, GOODBYE STORED
- SQLで読み取ってみる
mysql> SELECT * FROM test.demo_test; +----+------------------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | +----+------------------+------+------+------+ | AA | HELLO, HELLO | 8 | 0 | 0 | | BB | GOODBYE, GOODBYE | 10 | 1 | 0 | +----+------------------+------+------+------+ 2 rows in set (0.00 sec)
set BB 10 0 16 GOODBYE, GOODBYE
BBキーのレコードの値にGOODBYE, GOODBYEをセット- 10: 操作のフラグ
- memcachedには無視される
- 0: expiration time (TTL)
16: ブロックバイト長
expiration timeのテスト
set CC 10 10 15 EXPIRATION DEMO STORED
- 10秒すると読めなくなる
get CC VALUE CC 10 15 EXPIRATION DEMO END get CC VALUE CC 10 15 EXPIRATION DEMO END get CC END
- SQLで読むと
mysql> SELECT * FROM test.demo_test; +----+------------------+------+------+------------+ | c1 | c2 | c3 | c4 | c5 | +----+------------------+------+------+------------+ | AA | HELLO, HELLO | 8 | 0 | 0 | | BB | GOODBYE, GOODBYE | 10 | 1 | 0 | | CC | EXPIRATION DEMO | 10 | 2 | 1591403745 | +----+------------------+------+------+------------+ 3 rows in set (0.00 sec)
c4: 連番- setするたびに増える
c5: EXPIREする時刻のUNIXタイムスタンプ
【補】memcached APIのトランザクション分離レベル
- READ UNCOMMITTED.
- Dirty Readがおきるので注意する
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test.demo_test VALUES('DD', 'INSERTED VIA SQL', 12, 8, 0);
Query OK, 1 row affected (0.00 sec)
get DD VALUE DD 12 16 INSERTED VIA SQL END
- rollbackするともちろん読めなくなる
mysql> ROLLBACK; Query OK, 0 rows affected (0.01 sec)
get DD END
MySQL 5.6 Developer試験対策 6 MySQLの一般的な構文_2
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
- 大文字と小文字の区別、修飾名、別名、予約語の使用など、MySQLの識別子の実装について説明する
- コメントの構文を識別し、使用する
- プリペアード・ステートメントについて説明する。プリペアード・ステートメントを使用する
大文字と小文字の区別、修飾名、別名、予約語の使用など、MySQLの識別子の実装について説明する
言語構造
スキーマオブジェクト名
- PostgreSQLでいう「データベースオブジェクト」のこと
- スペ終わりは駄目:
mysql> CREATE TABLE `tbl `; ERROR 1103 (42000): Incorrect table name 'tbl '
- クォートを標準SQL準拠にしている場合は
""でも可
mysql> CREATE TABLE "interval" (begin INT, end INT); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"interval" (begin INT, end INT)' at line 1 mysql> SET sql_mode='ansi_quotes'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE "interval" (begin INT, end INT); Query OK, 0 rows affected (0.01 sec)
1eとかを使うのはやめようねという話
CREATE TABLE tbl(`1e` INT); INSERT INTO tbl VALUES (2); SELECT `1e`+1,1e+1 FROM tbl;
+--------+------+ | `1e`+1 | 1e+1 | +--------+------+ | 3 | 10 | +--------+------+ 1 row in set (0.00 sec)
- 識別子の長さ上限
識別子の修飾子
schema_name.table.name.column_nameみたいなやつ
mysql> CREATE TABLE .tbl(col INT); Query OK, 0 rows affected (0.01 sec)
- ODBC互換のためにテーブル名に
.を前置することが許可されている- デフォルトデータベース
関数名の構文解析と解決
mysql> SELECT COUNT(*) FROM tbl; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT (*) FROM tbl; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM tbl' at line 1 mysql> SET sql_mode='IGNORE_SPACE'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT COUNT (*) FROM tbl; +-----------+ | COUNT (*) | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec)
- デフォルトで関数名の後にスペースを空けることは認められない
SET sql_mode='IGNORE_SPACE';すると空けられるようになるSET sql_mode='ANSI;等、コンポジットモードに含まれている場合でも可
mysql> SET sql_mode='ANSI'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT COUNT (*) FROM tbl; +-----------+ | COUNT (*) | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec)
予約語
- 予約語を識別子として使いたい場合はbacktickで囲む
mysql> CREATE TABLE interval (begin INT, end INT); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval (begin INT, end INT)' at line 1 mysql> CREATE TABLE `interval` (begin INT, end INT); Query OK, 0 rows affected (0.02 sec)
- ピリオドの後は予約語でもクォート不要
mysql> CREATE TABLE sample.interval (begin INT, end INT); Query OK, 0 rows affected (0.01 sec)
- 歴史的経緯により例外的に大丈夫なものもある
- 多くの人が使っちゃってたので
mysql> CREATE TABLE date(d date); Query OK, 0 rows affected (0.01 sec)
- 大丈夫なやつ
- ACTION
- BIT
- DATE
- ENUM
- NO
- TEXT
- TIME
- TIMESTAMP
式の構文
NOTと!の優先順位の話- デフォルトで
!はNOTよりも優先順位が高い
mysql> SELECT NOT ! 1; +---------+ | NOT ! 1 | +---------+ | 1 | +---------+ 1 row in set (0.01 sec) mysql> SELECT NOT NOT 1; +-----------+ | NOT NOT 1 | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT ! ! 1; +-------+ | ! ! 1 | +-------+ | 1 | +-------+ 1 row in set (0.00 sec) mysql> SELECT ! NOT 1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT 1' at line 1
- ので、
!がNOTと結合してエラーになる模様 - これを避けるには、
sql_modeにHIGH_NOT_PRECEDENCEを設定して!とNOTの優先順位を同じにする
mysql> SET sql_mode = 'high_not_precedence'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT NOT ! 1; +---------+ | NOT ! 1 | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql> SELECT NOT NOT 1; +-----------+ | NOT NOT 1 | +-----------+ | 1 | +-----------+ 1 row in set (0.01 sec) mysql> SELECT ! ! 1; +-------+ | ! ! 1 | +-------+ | 1 | +-------+ 1 row in set (0.00 sec) mysql> SELECT ! NOT 1; +---------+ | ! NOT 1 | +---------+ | 1 | +---------+ 1 row in set (0.00 sec)
コメントの構文を識別し、使用する
SELECT 1+1; #hogehoge SELECT 1+1; -- piyopiyo SELECT 1+ /* multiple-line comment */ 1;
これは駄目:
mysql> SELECT 1+1; --fugafuga
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (0.00 sec)
-> ;
;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--fugafuga' at line 1
--の後はスペースを入れないと単項マイナス2つと解釈される
mysql> SELECT --1; +-----+ | --1 | +-----+ | 1 | +-----+ 1 row in set (0.00 sec)
- MySQLでのみ実行されるコメント
CREATE TABLE tbl(id INT, col INT); INSERT INTO tbl VALUES (1,1),(2,1),(3,2),(4,NULL);
SELECT t1.id AS t1id , t2.id AS t2id , t2.col FROM tbl t1 /*! LEFT */ JOIN tbl t2 ON (t1.id = t2.col);
+------+------+------+ | t1id | t2id | col | +------+------+------+ | 1 | 1 | 1 | | 1 | 2 | 1 | | 2 | 3 | 2 | | 3 | NULL | NULL | | 4 | NULL | NULL | +------+------+------+ 5 rows in set (0.00 sec)
- あまりいい例じゃない
- 普通はMySQL特有のキーワードに対して
/*! */コメントを付ける- 例:
STRAIGHT_JOIN,HIGH_PRIORITYなど
- 例:
プリペアード・ステートメントについて説明する。プリペアード・ステートメントを使用する
ユーザー定義変数
こういうやつ:
mysql> SELECT @x; +------+ | @x | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> SET @x = 1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @x; +------+ | @x | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> SELECT @x := @x+1; +------------+ | @x := @x+1 | +------------+ | 2 | +------------+ 1 row in set (0.00 sec)
プリペアドステートメント
- ユーザー定義変数を使う
mysql> SET @s = 'SELECT ? FROM DUAL'; Query OK, 0 rows affected (0.00 sec) mysql> PREPARE stmt FROM @s; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> SET @a = 1; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE stmt USING @a; +---+ | ? | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.00 sec)
MySQL 5.6 Developer試験対策 7 MySQLのストアド・プログラムの設計、作成および使用
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
- トリガーについて説明する。トリガーを使用する
- ストアド・プロシージャを作成する。ストアド・プロシージャを実行する
- ストアド・ファンクションを作成する。ストアド・ファンクションを使用する
- 【補】ストアドルーチン共通
- ストアド・プロシージャ内にエラー処理を実装する
MySQLのストアド・プログラムの設計、作成および使用
トリガーについて説明する。トリガーを使用する
トリガーの構文と例
CREATE DATABASE IF NOT EXISTS sample; USE sample; CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
- mysql データベースだと駄目だった
ERROR 1465 (HY000): Triggers can not be created on system tables
mysql> SELECT @sum FROM DUAL; +------+ | @sum | +------+ | NULL | +------+ 1 row in set (0.00 sec)
- ユーザ変数を初期化
mysql> SET @sum = 0; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @sum FROM DUAL; +------+ | @sum | +------+ | 0 | +------+ 1 row in set (0.00 sec)
INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); SELECT @sum;
+---------+ | @sum | +---------+ | 1852.48 | +---------+ 1 row in set (0.00 sec)
- TRIGGERに直接ロジックを書くのではなく、ストアドプロシージャに逃したほうが簡単になることも
トリガーのメタデータ
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G;
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: sample
TRIGGER_NAME: ins_sum
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: sample
EVENT_OBJECT_TABLE: account
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: SET @sum = @sum + NEW.amount
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE: NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)
ERROR:
No query specified
SHOW CREATE TRIGGER文- 特定のトリガーを定義した
CREATE TRIGGER文の確認等
- 特定のトリガーを定義した
SHOW CREATE TRIGGER ins_sum\G;
*************************** 1. row ***************************
Trigger: ins_sum
sql_mode: NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
ERROR:
No query specified
SHOW TRIGGERS文- triggerの一覧
SHOW TRIGGERS\G;
*************************** 1. row ***************************
Trigger: ins_sum
Event: INSERT
Table: account
Statement: SET @sum = @sum + NEW.amount
Timing: BEFORE
Created: NULL
sql_mode: NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
ERROR:
No query specified
ストアド・プロシージャを作成する。ストアド・プロシージャを実行する
mysql> CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;
CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
mysql> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPEAT SET @x = @x + 1' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNTIL @x > p1 END REPEAT' at line 1
mysql> END;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1
CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
- BEGIN-ENDの中のボディの
;でCREATE PROCEDURE文が断ち切られてしまう - ので、一時的にデリミタを再定義する
\はやめよう
delimiter // CREATE PROCEDURE dorepeat(p1 INT) BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END// delimiter ;
- PostgreSQLと異なり、オーバーロードはできない
delimiter // CREATE PROCEDURE dorepeat(p1 INT, p2 INT) BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END// delimiter ;
ERROR 1304 (42000): PROCEDURE dorepeat already exists
- PROCEDUREはCALLで呼ぶ
mysql> CALL dorepeat(1000); CALL dorepeat(1000); Query OK, 0 rows affected (0.01 sec) mysql> SELECT @x FROM DUAL; SELECT @x FROM DUAL; +------+ | @x | +------+ | 1001 | +------+ 1 row in set (0.00 sec)
ストアドプロシージャのメタデータ
SHOW CREATE PROCEDURE dorepeat\G;
*************************** 1. row ***************************
Procedure: dorepeat
sql_mode: NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `dorepeat`(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> SHOW PROCEDURE STATUS LIKE 'dorepeat'\G;
SHOW PROCEDURE STATUS LIKE 'dorepeat'\G;
*************************** 1. row ***************************
Db: sample
Name: dorepeat
Type: PROCEDURE
Definer: root@localhost
Modified: 2020-06-03 17:05:38
Created: 2020-06-03 17:05:38
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
ERROR:
No query specified
SHOW PROCEDURESとかはない
ストアド・ファンクションを作成する。ストアド・ファンクションを使用する
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN CONCAT('Hello, ',s,'!');
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
- テーブルとかは返せない
- テーブルのSELECTクエリ結果の表示だけならPROCEDUREでできる
delimiter // CREATE PROCEDURE hoge(num int) BEGIN SELECT num FROM DUAL; END// delimiter ;
- 再帰関数にはできない
メタデータ
SHOW CREATE FUNCTION hello\G;
*************************** 1. row ***************************
Function: hello
sql_mode: NO_ENGINE_SUBSTITUTION
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s CHAR(20)) RETURNS char(50) CHARSET latin1
DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!')
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
ERROR:
No query specified
SHOW FUNCTION STATUS LIKE 'hello'\G;
*************************** 1. row ***************************
Db: sample
Name: hello
Type: FUNCTION
Definer: root@localhost
Modified: 2020-06-03 17:08:11
Created: 2020-06-03 17:08:11
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
ERROR:
No query specified
SHOW FUNCTIONSとかはない
【補】ストアドルーチン共通
- EXECUTE 権限必要
- SQL SECURITY特性
- DEFINER / INVOKER
- 定義側のコンテキストで実行するか、呼び出し元のコンテキストで実行するか
- DEFINER / INVOKER
メタデータ
SELECT * FROM INFORMATION_SCHEMA.ROUTINES\G;
*************************** 1. row ***************************
SPECIFIC_NAME: dorepeat
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: sample
ROUTINE_NAME: dorepeat
ROUTINE_TYPE: PROCEDURE
DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2020-06-03 17:05:38
LAST_ALTERED: 2020-06-03 17:05:38
SQL_MODE: NO_ENGINE_SUBSTITUTION
ROUTINE_COMMENT:
DEFINER: root@localhost
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
DATABASE_COLLATION: latin1_swedish_ci
*************************** 2. row ***************************
SPECIFIC_NAME: hello
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: sample
ROUTINE_NAME: hello
ROUTINE_TYPE: FUNCTION
DATA_TYPE: char
CHARACTER_MAXIMUM_LENGTH: 50
CHARACTER_OCTET_LENGTH: 50
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: latin1
COLLATION_NAME: latin1_swedish_ci
DTD_IDENTIFIER: char(50)
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: RETURN CONCAT('Hello, ',s,'!')
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: YES
SQL_DATA_ACCESS: CONTAINS SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2020-06-03 17:08:11
LAST_ALTERED: 2020-06-03 17:08:11
SQL_MODE: NO_ENGINE_SUBSTITUTION
ROUTINE_COMMENT:
DEFINER: root@localhost
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
DATABASE_COLLATION: latin1_swedish_ci
2 rows in set (0.00 sec)
ERROR:
No query specified
ストアド・プロシージャ内にエラー処理を実装する
- 死なずに続行するか(
CONTINUE)、BEGIN-ENDブロックを離脱するか(EXIT)UNDOもパースできるがサポートされていない
CREATE TABLE sample.t (s1 INT, PRIMARY KEY (s1)); delimiter // CREATE PROCEDURE handlerdemo () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1; INSERT INTO sample.t VALUES (1); SET @x = 2; INSERT INTO sample.t VALUES (1); SET @x = 3; END// delimiter ; CALL handlerdemo(); SELECT @x;
+------+ | @x | +------+ | 3 | +------+ 1 row in set (0.00 sec)
CONTINUEハンドラがないと主キー重複でエラーになる
CREATE TABLE sample.t (s1 INT, PRIMARY KEY (s1)); delimiter // CREATE PROCEDURE handlerdemo () BEGIN -- DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1; INSERT INTO sample.t VALUES (1); SET @x = 2; INSERT INTO sample.t VALUES (1); SET @x = 3; END// delimiter ; CALL handlerdemo(); SELECT @x;
+------+ | @x | +------+ | 2 | +------+ 1 row in set (0.00 sec)
SHOW ERRORS; +-------+------+---------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------+ | Error | 1062 | Duplicate entry '1' for key 'PRIMARY' | +-------+------+---------------------------------------+ 1 row in set (0.00 sec)
MySQL 5.6 Developer試験対策 8 データの修正
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
- INSERT文について説明する。INSERT文を実行する
- REPLACE文について説明する。REPLACE文を実行する
- UPDATE文について説明する。UPDATE文を実行する
- TRUNCATE文について説明する。TRUNCATE文を実行する
- LOAD DATA文について説明する。LOAD DATA文を実行する
- DELETE文について説明する。DELETE文を実行する
INSERT文について説明する。INSERT文を実行する
INSERT ... SELECT
- 連番を突っ込むのとかに使ってる
- カラム名とかは関係ないので注意
mysql> CREATE TABLE tbl(col int, col2 int);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tbl(col, col2)
SELECT num * num AS col2,
num AS col
FROM seq;
Query OK, 1000 rows affected (0.01 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tbl ORDER BY col LIMIT 10;
+------+------+
| col | col2 |
+------+------+
| 0 | 0 |
| 1 | 1 |
| 4 | 2 |
| 9 | 3 |
| 16 | 4 |
| 25 | 5 |
| 36 | 6 |
| 49 | 7 |
| 64 | 8 |
| 81 | 9 |
+------+------+
10 rows in set (0.00 sec)
- 単なるVALUESの糖衣と考えたほうがよさそう
- 【所感】select listに順序を要求するのがリレーショナルモデル的に気持ち悪い…
INSERT DELAYED
- 5.6時点でdeprecated
- 8.0で消滅
- 複数セッションから同時に大量行を挿入するようなケースで高速化が期待できる
- 逆にそのようなケースでなければ普通のINSERTのほうが速い
- MyISAM等で利用可能
- InnoDB不可
mysql> CREATE TABLE tbl(col INT) ENGINE=InnoDB; CREATE TABLE tbl(col INT) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> INSERT DELAYED INTO tbl VALUES (1); INSERT DELAYED INTO tbl VALUES (1); ERROR 1616 (HY000): DELAYED option not supported for table 'tbl'
mysql> CREATE TABLE tbl(col INT) ENGINE=MyISAM; CREATE TABLE tbl(col INT) ENGINE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> INSERT DELAYED INTO tbl VALUES (1); INSERT DELAYED INTO tbl VALUES (1); Query OK, 1 row affected, 1 warning (0.00 sec)
INSERT ... ON DUPLICATE KEY UPDATE
- 後述のREPLACEと異なり、こちらは更新なので
AUTO_INCREMENTが増加しない
CREATE TABLE tbl( id INT PRIMARY KEY AUTO_INCREMENT, code CHAR(4) UNIQUE, name VARCHAR(32) );
mysql> INSERT INTO tbl(code, name) VALUES ('xxxx', 'old')
ON DUPLICATE KEY UPDATE
name = VALUES(name);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tbl;
+----+------+------+
| id | code | name |
+----+------+------+
| 1 | xxxx | old |
+----+------+------+
1 row in set (0.00 sec)
mysql> INSERT INTO tbl(code, name) VALUES ('xxxx', 'new')
ON DUPLICATE KEY UPDATE
name = VALUES(name);
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM tbl;
+----+------+------+
| id | code | name |
+----+------+------+
| 1 | xxxx | new |
+----+------+------+
1 row in set (0.00 sec)
REPLACE文について説明する。REPLACE文を実行する
REPLACE は、INSERT とまったく同じように機能します。ただし、テーブル内の古い行に、PRIMARY KEY または UNIQUE インデックスに関して新しい行と同じ値が含まれている場合、その古い行は新しい行が挿入される前に削除されます。
- 挿入 or (削除 + 挿入)
- cf. 挿入 or 更新は
INSERT ... ON DUPLICATE KEY UPDATE
- cf. 挿入 or 更新は
- 候補キー的なものがない限りINSERTと全く同じ
CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, data VARCHAR(64) DEFAULT NULL, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) ); CREATE TABLE test2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, data VARCHAR(64) DEFAULT NULL, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id, ts) );
mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00'); REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00'); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42'); REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42'); Query OK, 2 rows affected (0.01 sec) mysql> SELECT * FROM test; SELECT * FROM test; +----+------+---------------------+ | id | data | ts | +----+------+---------------------+ | 1 | New | 2014-08-20 18:47:42 | +----+------+---------------------+ 1 row in set (0.00 sec)
mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00'); REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00'); Query OK, 1 row affected (0.01 sec) mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42'); REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test2; SELECT * FROM test2; +----+------+---------------------+ | id | data | ts | +----+------+---------------------+ | 1 | Old | 2014-08-20 18:47:00 | | 1 | New | 2014-08-20 18:47:42 | +----+------+---------------------+ 2 rows in set (0.00 sec)
- 削除+挿入なので、自動インクリメントは増加することに注意する
CREATE TABLE tbl( id INT PRIMARY KEY AUTO_INCREMENT, code CHAR(4) UNIQUE, name VARCHAR(32) );
mysql> REPLACE INTO tbl(code, name) VALUES ('xxxx', 'old');
REPLACE INTO tbl(code, name) VALUES ('xxxx', 'old');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+----+------+------+
| id | code | name |
+----+------+------+
| 1 | xxxx | old |
+----+------+------+
1 row in set (0.00 sec)
mysql> REPLACE INTO tbl(code, name) VALUES ('xxxx', 'new');
REPLACE INTO tbl(code, name) VALUES ('xxxx', 'new');
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+----+------+------+
| id | code | name |
+----+------+------+
| 2 | xxxx | new |
+----+------+------+
1 row in set (0.00 sec)
UPDATE文について説明する。UPDATE文を実行する
- 複数テーブルの更新ができる
CREATE TABLE tbl(id int PRIMARY KEY AUTO_INCREMENT); CREATE TABLE tbl2 ( parent_id INT, num INT, FOREIGN KEY (parent_id) REFERENCES tbl(id) ); INSERT INTO tbl VALUES (1),(2),(3); INSERT INTO tbl2 VALUES (1, 10),(1, 20),(2, 30),(2, 40),(NULL, 50); SELECT * FROM tbl INNER JOIN tbl2 ON tbl.id = tbl2.parent_id;
+----+-----------+------+ | id | parent_id | num | +----+-----------+------+ | 1 | 1 | 10 | | 1 | 1 | 20 | | 2 | 2 | 30 | | 2 | 2 | 40 | +----+-----------+------+ 4 rows in set (0.00 sec)
- 更新
UPDATE tbl INNER JOIN tbl2 ON tbl.id=tbl2.parent_id SET tbl2.num = tbl2.num - 5;
Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0
mysql> SELECT * FROM tbl INNER JOIN tbl2 ON tbl.id = tbl2.parent_id; +----+-----------+------+ | id | parent_id | num | +----+-----------+------+ | 1 | 1 | 5 | | 1 | 1 | 15 | | 2 | 2 | 25 | | 2 | 2 | 35 | +----+-----------+------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM tbl2; +-----------+------+ | parent_id | num | +-----------+------+ | 1 | 5 | | 1 | 15 | | 2 | 25 | | 2 | 35 | | NULL | 50 | +-----------+------+ 5 rows in set (0.00 sec)
TRUNCATE文について説明する。TRUNCATE文を実行する
- DELETEとの違い
- FKの親テーブルのTRUNCATEは怒られる
- 参照されている行が無くても
mysql> CREATE TABLE tbl(id int PRIMARY KEY AUTO_INCREMENT); CREATE TABLE tbl(id int PRIMARY KEY AUTO_INCREMENT); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE tbl2(parent_id int, FOREIGN KEY (parent_id) REFERENCES tbl(id)); CREATE TABLE tbl2(parent_id int, FOREIGN KEY (parent_id) REFERENCES tbl(id)); Query OK, 0 rows affected (0.01 sec) mysql> TRUNCATE tbl; TRUNCATE tbl; ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`mysql`.`tbl2`, CONSTRAINT `tbl2_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `mysql`.`tbl` (`id`))
- 【補】FK制約チェックを外せば可能
mysql> SET foreign_key_checks='OFF'; SET foreign_key_checks='OFF'; Query OK, 0 rows affected (0.00 sec) mysql> TRUNCATE tbl; TRUNCATE tbl; Query OK, 0 rows affected (0.02 sec)
LOAD DATA文について説明する。LOAD DATA文を実行する
- INSERTよりはやいやつ
- LOCAL: クライアント側のファイルを読み込むの意
- 【補】PostgreSQLのpsqlの
\copyコマンド相当
- 【補】PostgreSQLのpsqlの
- LOCALなし: サーバー側のファイルを読み込むの意
- 【補】PostgreSQLの
COPYステートメント相当
- 【補】PostgreSQLの
DELETE文について説明する。DELETE文を実行する
- JOIN結果をDELETEできる
- どのテーブルの行をDELETEするかを指定する必要がある
CREATE TABLE tbl(id int PRIMARY KEY AUTO_INCREMENT); CREATE TABLE tbl2 ( id INT, parent_id INT, FOREIGN KEY (parent_id) REFERENCES tbl(id) ); INSERT INTO tbl VALUES (1),(2),(3); INSERT INTO tbl2 VALUES (1, 1),(2, 1),(3, 2),(4, 2),(5, NULL); SELECT * FROM tbl INNER JOIN tbl2 ON tbl.id = tbl2.parent_id;
+----+------+-----------+ | id | id | parent_id | +----+------+-----------+ | 1 | 1 | 1 | | 1 | 2 | 1 | | 2 | 1 | 2 | | 2 | 2 | 2 | +----+------+-----------+ 4 rows in set (0.00 sec)
- 親に紐付いた子の行のみ削除される
DELETE tbl2 FROM tbl INNER JOIN tbl2 ON tbl.id = tbl2.parent_id;
mysql> SELECT * FROM tbl
INNER JOIN tbl2
ON tbl.id = tbl2.parent_id;
SELECT * FROM tbl
-> INNER JOIN tbl2
-> ON tbl.id = tbl2.parent_id;
Empty set (0.00 sec)
mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
mysql> SELECT * FROM tbl2;
SELECT * FROM tbl2;
+------+-----------+
| id | parent_id |
+------+-----------+
| 5 | NULL |
+------+-----------+
1 row in set (0.00 sec)
- なお、親の行は削除できない(ことがある？)
TRUNCATE TABLE tbl2; INSERT INTO tbl2 VALUES (1, 1),(2, 1),(3, 2),(4, 2),(5, NULL); DELETE tbl, tbl2 FROM tbl INNER JOIN tbl2 ON tbl.id = tbl2.parent_id;
外部キー制約が存在する InnoDB テーブルを含む、複数テーブルの DELETE ステートメントを使用した場合は、MySQL オプティマイザが、それらの親子関係の順序とは異なる順序でテーブルを処理する可能性があります。
この場合、このステートメントは失敗し、ロールバックされます。
代わりに、1 つのテーブルから削除したあと、InnoDB が提供する ON DELETE 機能を使用して、ほかのテーブルがそれに応じて変更されるようにしてください。
MySQL 5.6 Developer試験対策 6 MySQLの一般的な構文_1
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
大文字と小文字の区別、修飾名、別名、予約語の使用など、MySQLの識別子の実装について説明する
言語構造
リテラル値
文字列リテラル
| 文字列リテラル | カラム名 | |
|---|---|---|
| 標準SQL | ' |
" |
| MySQL | ', " |
` |
- PostgreSQLなんかは標準準拠
ANSI_QUOTESSQLモードだと標準準拠になる
SELECT @@GLOBAL.sql_mode; +------------------------+ | @@GLOBAL.sql_mode | +------------------------+ | NO_ENGINE_SUBSTITUTION | +------------------------+ 1 row in set (0.00 sec)
SELECT "hoge"; +------+ | hoge | +------+ | hoge | +------+ 1 row in set (0.00 sec)
mysql> SET sql_mode='ANSI_QUOTES'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT "hoge"; SELECT "hoge"; ERROR 1054 (42S22): Unknown column 'hoge' in 'field list'
- 文字列リテラルの連結
SELECT 'a' ' ' 'string'; +----------+ | a | +----------+ | a string | +----------+ 1 row in set (0.00 sec)
- バイナリ文字列
- 文字セット、照合順序をもたないバイト文字列
- 非バイナリ文字列
- 文字セット、照合順序をもつバイト文字列
SELECT _latin1'string' COLLATE latin1_danish_ci; +------------------------------------------+ | _latin1'string' COLLATE latin1_danish_ci | +------------------------------------------+ | string | +------------------------------------------+ 1 row in set (0.00 sec)
mysql> SELECT ''''; SELECT ''''; +---+ | ' | +---+ | ' | +---+ 1 row in set (0.00 sec) mysql> SELECT '\''; SELECT '\''; +---+ | ' | +---+ | ' | +---+ 1 row in set (0.00 sec) mysql> SELECT "'"; SELECT "'"; +---+ | ' | +---+ | ' | +---+ 1 row in set (0.00 sec)
'' ''は空文字列2つが連結されて空文字列になる
mysql> SELECT '' ''; SELECT '' ''; +--+ | | +--+ | | +--+ 1 row in set (0.00 sec)
数値リテラル
SELECT 1, .2, 3.4, -5, --6, +7; +---+-----+-----+----+-----+---+ | 1 | .2 | 3.4 | -5 | --6 | 7 | +---+-----+-----+----+-----+---+ | 1 | 0.2 | 3.4 | -5 | 6 | 7 | +---+-----+-----+----+-----+---+ 1 row in set (0.00 sec)
SELECT 1.2e3; +-------+ | 1.2e3 | +-------+ | 1200 | +-------+ 1 row in set (0.00 sec)
日付リテラルと時間リテラル
SELECT DATE '20200202',TIME '235959',TIMESTAMP '20200202235959'; +-----------------+---------------+----------------------------+ | DATE '20200202' | TIME '235959' | TIMESTAMP '20200202235959' | +-----------------+---------------+----------------------------+ | 2020-02-02 | 23:59:59 | 2020-02-02 23:59:59 | +-----------------+---------------+----------------------------+ 1 row in set (0.00 sec)
- デリミタは割と融通が効く
SELECT DATE'2020/02/02', DATE'2020-02-02', DATE'2020!02!02'; +------------------+------------------+------------------+ | DATE'2020/02/02' | DATE'2020-02-02' | DATE'2020!02!02' | +------------------+------------------+------------------+ | 2020-02-02 | 2020-02-02 | 2020-02-02 | +------------------+------------------+------------------+ 1 row in set (0.00 sec)
- まぜても大丈夫
SELECT DATE'2020!02^02'; +------------------+ | DATE'2020!02^02' | +------------------+ | 2020-02-02 | +------------------+ 1 row in set (0.00 sec)
- ASCII 32番(SPC)以下は駄目な感じがある
SELECT DATE'2020 02 02'; ERROR 1525 (HY000): Incorrect DATE value: '2020 02 02'
- スペースはオプショナル
SELECT DATE'20200202',TIME'235959',TIMESTAMP'20200202235959'; +----------------+--------------+---------------------------+ | DATE'20200202' | TIME'235959' | TIMESTAMP'20200202235959' | +----------------+--------------+---------------------------+ | 2020-02-02 | 23:59:59 | 2020-02-02 23:59:59 | +----------------+--------------+---------------------------+ 1 row in set (0.00 sec)
ODBC構文も認識する
SELECT {d'20200202'},{t'235959'},{ts'20200202235959'};
+---------------+-------------+----------------------+
| {d'20200202'} | {t'235959'} | {ts'20200202235959'} |
+---------------+-------------+----------------------+
| 2020-02-02 | 23:59:59 | 2020-02-02 23:59:59 |
+---------------+-------------+----------------------+
1 row in set (0.00 sec)
DATE
- 不正な日付
- 月や日の値域が不正な場合
mysql> SELECT DATE'071332'; SELECT DATE'071332'; ERROR 1525 (HY000): Incorrect DATE value: '071332'
- テーブルの列だとまた違う
mysql> CREATE TABLE tbl (d date);
CREATE TABLE tbl (d date);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO tbl VALUES ('071332');
INSERT INTO tbl VALUES ('071332');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'd' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+------------+
| d |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)
- ゼロ日付
mysql> SELECT DATE'000000'; SELECT DATE'000000'; +--------------+ | DATE'000000' | +--------------+ | 0000-00-00 | +--------------+ 1 row in set (0.00 sec)
- しかるべき
sql_modeを設定すればゼロ日付も不正として弾ける
mysql> SET sql_mode='no_zero_date'; SET sql_mode='no_zero_date'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SELECT DATE'000000'; SELECT DATE'000000'; ERROR 1525 (HY000): Incorrect DATE value: '000000'
TIMESTAMP
- 日と時の間の分割には
、Tが使える
SELECT TIMESTAMP'2020-01-01 23:59:59',TIMESTAMP'2020-01-01T23:59:59'; +--------------------------------+--------------------------------+ | TIMESTAMP'2020-01-01 23:59:59' | TIMESTAMP'2020-01-01T23:59:59' | +--------------------------------+--------------------------------+ | 2020-01-01 23:59:59 | 2020-01-01 23:59:59 | +--------------------------------+--------------------------------+ 1 row in set (0.00 sec)
- 一切区切らないことも可
SELECT TIMESTAMP'20200101235959'; +---------------------------+ | TIMESTAMP'20200101235959' | +---------------------------+ | 2020-01-01 23:59:59 | +---------------------------+ 1 row in set (0.00 sec)
- 2038年問題がある
- オンメモリは大丈夫
mysql> SELECT TIMESTAMP'2038-01-01 00:00:00'; SELECT TIMESTAMP'2038-01-01 00:00:00'; +--------------------------------+ | TIMESTAMP'2038-01-01 00:00:00' | +--------------------------------+ | 2038-01-01 00:00:00 | +--------------------------------+ 1 row in set (0.00 sec) mysql> SELECT TIMESTAMP'2039-01-01 00:00:00'; SELECT TIMESTAMP'2039-01-01 00:00:00'; +--------------------------------+ | TIMESTAMP'2039-01-01 00:00:00' | +--------------------------------+ | 2039-01-01 00:00:00 | +--------------------------------+ 1 row in set (0.00 sec)
- 永続化すると警告が出、ゼロ日時が入る
mysql> CREATE TABLE tbl(ts TIMESTAMP);
CREATE TABLE tbl(ts TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tbl VALUES ('2038-01-01 00:00:00');
INSERT INTO tbl VALUES ('2038-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tbl VALUES ('2039-01-01 00:00:00');
INSERT INTO tbl VALUES ('2039-01-01 00:00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'ts' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+---------------------+
| ts |
+---------------------+
| 2038-01-01 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO tbl '';
INSERT INTO tbl '';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' at line 1
YYMMDD- YYが[70,99]: 20世紀
- YYが[00,69]:21正規
mysql> SELECT DATE '700101'; SELECT DATE '700101'; +---------------+ | DATE '700101' | +---------------+ | 1970-01-01 | +---------------+ 1 row in set (0.00 sec) mysql> SELECT DATE '691231'; SELECT DATE '691231'; +---------------+ | DATE '691231' | +---------------+ | 2069-12-31 | +---------------+ 1 row in set (0.00 sec)
TIME
mysql> SELECT TIME'12:34:56'; SELECT TIME'12:34:56'; +----------------+ | TIME'12:34:56' | +----------------+ | 12:34:56 | +----------------+ 1 row in set (0.00 sec) mysql> SELECT TIME'12:34'; SELECT TIME'12:34'; +-------------+ | TIME'12:34' | +-------------+ | 12:34:00 | +-------------+ 1 row in set (0.00 sec) mysql> SELECT TIME'56'; SELECT TIME'56'; +----------+ | TIME'56' | +----------+ | 00:00:56 | +----------+ 1 row in set (0.00 sec)
- 日(D)をスペース区切りで指定できる
- [0,34]
- 【疑問点】何これ？？？？？
- 2桁指定時の挙動が変わる
- [0,34]
mysql> SELECT TIME'0 12:34:56'; SELECT TIME'0 12:34:56'; +------------------+ | TIME'0 12:34:56' | +------------------+ | 12:34:56 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT TIME'0 12:34'; SELECT TIME'0 12:34'; +---------------+ | TIME'0 12:34' | +---------------+ | 12:34:00 | +---------------+ 1 row in set (0.00 sec) mysql> SELECT TIME'0 12'; SELECT TIME'0 12'; +------------+ | TIME'0 12' | +------------+ | 12:00:00 | +------------+ 1 row in set (0.00 sec)
- デリミタ指定時は各桁0埋めする必要なし
mysql> SELECT TIME'123'; SELECT TIME'123'; +-----------+ | TIME'123' | +-----------+ | 00:01:23 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT TIME'1:2:3'; SELECT TIME'1:2:3'; +-------------+ | TIME'1:2:3' | +-------------+ | 01:02:03 | +-------------+ 1 row in set (0.00 sec)
- マイクロ秒までを設定できる
SELECT TIME'12:34:56.123456'; +-----------------------+ | TIME'12:34:56.123456' | +-----------------------+ | 12:34:56.123456 | +-----------------------+ 1 row in set (0.00 sec)
- マイクロ秒は
TIME型カラムには永続化できない- 警告も出ない
mysql> CREATE TABLE tbl (t TIME);
CREATE TABLE tbl (t TIME);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO tbl VALUES ('12:34:56.123456');
INSERT INTO tbl VALUES ('12:34:56.123456');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+----------+
| t |
+----------+
| 12:34:56 |
+----------+
1 row in set (0.00 sec)
type_name(fsp)型カラムに永続化する
mysql> CREATE TABLE tbl2 (t TIME(3));
CREATE TABLE tbl2 (t TIME(3));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO tbl2 VALUES ('12:34:56.123456');
INSERT INTO tbl2 VALUES ('12:34:56.123456');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tbl2;
SELECT * FROM tbl2;
+--------------+
| t |
+--------------+
| 12:34:56.123 |
+--------------+
1 row in set (0.00 sec)
16進数リテラル
- バイナリ文字列のように機能
SELECT X'41'; +-------+ | X'41' | +-------+ | A | +-------+ 1 row in set (0.00 sec)
- 数値に変換できる
SELECT X'41' + 0; +-----------+ | X'41' + 0 | +-----------+ | 65 | +-----------+ 1 row in set (0.00 sec)
- まじめに変換するなら
CAST
SELECT 0x41, 0x41+0, CAST(0x41 AS UNSIGNED INT); +------+--------+----------------------------+ | 0x41 | 0x41+0 | CAST(0x41 AS UNSIGNED INT) | +------+--------+----------------------------+ | A | 65 | 65 | +------+--------+----------------------------+ 1 row in set (0.00 sec)
- 逆変換:
HEX関数- バイナリ文字列 -> 16進数
- 10進数 -> 16進数
SELECT HEX('A'), HEX(65);
+----------+---------+
| HEX('A') | HEX(65) |
+----------+---------+
| 41 | 41 |
+----------+---------+
1 row in set (0.00 sec)
booleanリテラル
SELECT TRUE,FALSE,true,false; +------+-------+------+-------+ | TRUE | FALSE | TRUE | FALSE | +------+-------+------+-------+ | 1 | 0 | 1 | 0 | +------+-------+------+-------+ 1 row in set (0.00 sec)
ビットフィールドリテラル
mysql> CREATE TABLE tbl (b BIT(8)); CREATE TABLE tbl (b BIT(8)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO tbl VALUES (255); INSERT INTO tbl VALUES (255); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tbl SET b = b'1010'; INSERT INTO tbl SET b = b'1010'; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tbl SET b = b'0101'; INSERT INTO tbl SET b = b'0101'; Query OK, 1 row affected (0.02 sec) mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM tbl; SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM tbl; +------+----------+----------+----------+ | b+0 | BIN(b+0) | OCT(b+0) | HEX(b+0) | +------+----------+----------+----------+ | 255 | 11111111 | 377 | FF | | 10 | 1010 | 12 | A | | 5 | 101 | 5 | 5 | +------+----------+----------+----------+ 3 rows in set (0.00 sec)
- 【補】
- SET型の内部表現はビットフィールドだったはず
- ユーザ変数のビット値
- バイナリ文字列として扱われる
mysql> SET @v1 = 0b1000001; SET @v1 = 0b1000001; Query OK, 0 rows affected (0.00 sec) mysql> SET @v2 = CAST(0b1000001 AS UNSIGNED); SET @v2 = CAST(0b1000001 AS UNSIGNED); Query OK, 0 rows affected (0.00 sec) mysql> SET @v3 = 0b1000001+0; SET @v3 = 0b1000001+0; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @v1,@v2,@v3; SELECT @v1,@v2,@v3; +------+------+------+ | @v1 | @v2 | @v3 | +------+------+------+ | A | 65 | 65 | +------+------+------+ 1 row in set (0.00 sec)
NULL値
- 【補】NULLは値ではありません
私たちは、まず「失われてはいるが、適用可能な値」を示すマークの定義から始めよう。これを「A-Mark」と呼ぶ。
このマークは、DBMS において値（value）としても変数（variable）としても扱われない。 (エドガー・F・コッド)NULL に関する大切なことは、厳密には NULL が値ではないということである。 (クリス・デイト)
- LOAD DATA INFILEでは
\Nで表現
MySQL 5.6 Developer試験対策 5 基本的な最適化 -- 最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する -- SELECTステートメントの最適化 (途中まで3)
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
公式: 最適化の概要
最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する
SELECT ステートメントの最適化
Multi-Range Read の最適化
- Disk-Sweep Multi-Range Read (MRR)
- デフォルト有効、コストベース
mrr=on,mrr_cost_based=on,
- 試してみる
CREATE TABLE tbl( col1 INT, col2 INT, content TEXT, INDEX (col1), INDEX (col2) ); INSERT INTO tbl VALUES(1,1,'hoge'),(2,2,'piyo');
EXPLAIN SELECT * FROM tbl WHERE col1 < 1 AND col2 < 1; +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------+ | 1 | SIMPLE | tbl | range | col1,col2 | col1 | 5 | NULL | 1 | Using index condition; Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------+ 1 row in set (0.00 sec)
- MRR訊いてない
- コストベースをやめ、常にMRRを試みてみる
SET optimizer_switch='mrr_cost_based=off';
- 効いた
EXPLAIN SELECT * FROM tbl WHERE col1 < 1 AND col2 < 1; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+ | 1 | SIMPLE | tbl | range | col1,col2 | col1 | 5 | NULL | 1 | Using index condition; Using where; Using MRR | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+ 1 row in set (0.00 sec)
ORDER BY の最適化
- B+木インデックスを利用してソート要らず
DESC tbl; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | col1 | int(11) | YES | MUL | NULL | | | col2 | int(11) | YES | MUL | NULL | | | content | text | YES | | NULL | | +---------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
EXPLAIN SELECT col1 FROM tbl ORDER BY col1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
type: index
possible_keys: NULL
key: col1
key_len: 5
ref: NULL
rows: 2
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
Extra: Using index
- こういうのは駄目:
EXPLAIN SELECT ABS(col1) AS col1 FROM tbl ORDER BY col1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
type: index
possible_keys: NULL
key: col1
key_len: 5
ref: NULL
rows: 2
Extra: Using index; Using filesort
1 row in set (0.00 sec)
ERROR:
No query specified
Using index; Using filesort
- select listはカヴァリングインデックス、ソートはfilesortの意
GROUP BY の最適化
- インデックスを利用して一時テーブル作成を回避する
- すべての
GROUP BY句が同じインデックスから属性を参照し、順番に使用していること
CREATE TABLE multbl ( col1 INT, col2 INT, content TEXT, PRIMARY KEY(col1, col2) );
- 複合インデックス
(col1,col2) - 全使用
EXPLAIN SELECT col1,col2 FROM multbl GROUP BY col1,col2 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: multbl
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 1
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
col1のみ使用
EXPLAIN SELECT col1,col2 FROM multbl GROUP BY col1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: multbl
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 1
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
col2だけやcol2,col1のみ使用だとソートが生じてしまう
EXPLAIN SELECT col1,col2 FROM multbl GROUP BY col2 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: multbl
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 1
Extra: Using index; Using temporary; Using filesort
1 row in set (0.00 sec)
ERROR:
No query specified
EXPLAIN SELECT col1,col2 FROM multbl GROUP BY col2,col1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: multbl
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 1
Extra: Using index; Using filesort
1 row in set (0.00 sec)
ERROR:
No query specified
ルースインデックススキャン
- 集約関数がMINまたはMAXの場合、その算出もインデックスを使えて速い、という話
- Extraが
Using index for group-byになるらしい - 再現できなかった…
タイトインデックススキャン
複合インデックス(col1,col2,col3)において、GRUOP BY col1,col3はインデックスによる一時テーブル作成回避が効かない
EXPLAIN SELECT col1,MIN(col2),col3 FROM multbl GROUP BY col1,col3; +----+-------------+--------+-------+---------------+---------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | multbl | index | PRIMARY | PRIMARY | 12 | NULL | 1 | Using index; Using temporary; Using filesort | +----+-------------+--------+-------+---------------+---------+---------+------+------+----------------------------------------------+ 1 row in set (0.00 sec)
col1,col3とギャップがあっても、WHEREでギャップが埋まっていれば一時テーブルを回避できる
EXPLAIN SELECT col1,col2,col3 FROM multbl WHERE col2=1 GROUP BY col1,col3; +----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | multbl | index | PRIMARY | PRIMARY | 12 | NULL | 1 | Using where; Using index | +----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
