MySQL 5.6 Developer試験対策 6 MySQLの一般的な構文_3

MySQL勉強メモ

MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」

[https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882:embed:cite]

MySQL 5.6 リファレンスマニュアル

[https://dev.mysql.com/doc/refman/5.6/ja/:embed:cite]


MySQLのデータ型の特徴と適切な使用方法を識別する

[https://dev.mysql.com/doc/refman/5.6/ja/data-types.html:embed:cite]

数値型

整数型

CREATE TABLE tbl (a INT UNSIGNED, b INT);
INSERT INTO tbl VALUES (3, 2);
mysql> SELECT a-b FROM tbl;
+------+
| a-b  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT b-a FROM tbl;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '("sample"."tbl"."b" - "sample"."tbl"."a")'
  • 一方がunsignedだと結果もunsigned
  • この場合、2-3をunsignedに格納しようとしてエラーになる
  • NO_UNSIGNED_SUBTRACTION SQLモードを設定することでこの挙動を変えることができる
mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT b-a FROM tbl;
+------+
| b-a  |
+------+
|   -1 |
+------+
1 row in set (0.00 sec)
  • M, ZEROFILL
mysql> CREATE TABLE tbl(col INT(10) ZEROFILL);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tbl VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tbl;
+------------+
| col        |
+------------+
| 0000000003 |
+------------+
1 row in set (0.00 sec)
  • BOOL, BOOLEANTINYINT(1)のシノニム
mysql> CREATE TABLE tbl (col BOOL);
Query OK, 0 rows affected (0.01 sec)

mysql> DESC tbl;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col   | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
  • TRUE,FALSE自体1/0のシノニム
SELECT TRUE,FALSE FROM DUAL;
+------+-------+
| TRUE | FALSE |
+------+-------+
|    1 |     0 |
+------+-------+
1 row in set (0.00 sec)
  • すべての演算は符号付きBIGINT値またはDOUBLE値を用いて行われる
  • 型一覧
name synonym length (bit)
BIT(M) M (1-64)
BOOL TINYINT(1)
TINYINT(M) 8
SMALLINT(M) 16
MEDIUMINT(M) 24
INT(M) 32
INTEGER(M) INT(M)
BIGINT(M) 64
SERIAL BIGINT UNSIGNED
NOT NULL AUTO_INCREMENT UNIQUE

固定小数点数

  • DECIMAL[(M[,D])]
  • 正確な値を投入するには文字列で
mysql> CREATE TABLE tbl (col DEC(6,4));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tbl VALUES ('3.1415');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tbl;
+--------+
| col    |
+--------+
| 3.1415 |
+--------+
1 row in set (0.00 sec)
  • DEC,NUMERIC,FIXEDシノニムあり

浮動小数点数

  • FLOAT[(M,D)]

    • IEEE754 単精度 準拠
    • H/WやOSによって実際の範囲が狭まることはある
    • 演算はDOUBLE値で行われることに注意する
    • =使うな的な話
  • DOUBLE[(M,D)]

    • IEEE754 倍精度 準拠
    • H/WやOSによって実際の範囲が狭まることはある
  • REAL

    • DOUBLE(デフォルト),またはFLOATのシノニム
    • ‘REALASFLOAT’ SQLモードで切り替え

日付と時間型

  • TIMEは時刻(time point, 00:00:00から23:59:59.fspまで)だけでなく時間(duration)を表現できる

    • 838時間まで
    • 0x7FFFFFのDEC表現8388607に由来する

文字列型

CHAR/VARCHAR

  • VARCHARはバイト長を保持するために1-2バイト余計に消費する

    • 255バイトまでは+1, 256バイトからは+2
  • CHARの末尾空白の挙動

    • 一律でカラム定義の桁数だけ消費する
    • 基本的に空白を切り詰めたものと区別しない
    • sql_mode非依存
    • 例外: LIKEのみ空白を区別する
mysql> CREATE TABLE tbl(c CHAR(4));
Query OK, 0 rows affected (0.01 sec)

mysql> drop table tbl;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tbl(c CHAR(4) PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tbl VALUES ('a');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT c='a', c='a   ', c LIKE 'a', c LIKE 'a   ' FROM tbl;
+-------+----------+------------+---------------+
| c='a' | c='a   ' | c LIKE 'a' | c LIKE 'a   ' |
+-------+----------+------------+---------------+
|     1 |        1 |          1 |             0 |
+-------+----------+------------+---------------+
1 row in set (0.00 sec)

BINARY/VARBINARY

  • バイナリ文字列を格納する

    • cf. CHAR BINARYは非バイナリ文字列を格納し、バイナリ商号順序を用いる
  • BINARYは\0でパディングされる
mysql> CREATE TABLE tbl (c BINARY(3));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tbl SET c = 'a';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT HEX(c), c='a', c='a\0\0' FROM tbl;
+--------+-------+-----------+
| HEX(c) | c='a' | c='a\0\0' |
+--------+-------+-----------+
| 610000 |     0 |         1 |
+--------+-------+-----------+
1 row in set (0.00 sec)

BLOB/TEXT

  • VARBINARY/VARCHARとの違い

    • BLOB/TEXTは、インデックス作成時、プレフィックス長の指定が必要
    • BLOB/TEXTは、DEFAULT値をふくめられない
  • 長さ
値の長さ 値の長さ管理バイト長 合計
TINYBLOB/TINYTEXT L < 2^8 1 L+1
BLOB/TEXT L < 2^16 2 L+2
MEDIUMBLOB/MEDIUMTEXT L < 2^24 3 L+3
LONGBLOB/LONGTEXT L < 2^32 4 L+4

ENUM/SET

  • 文字列のなかま
  • SET型は数値コンテキストで取得するとビットセットを得る

    • ゆえに64要素まで(64ビット)
mysql> CREATE TABLE tbl(col SET('a','b','c','d'));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tbl VALUES ('a,c');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT col, col+0, BIN(col+0) FROM tbl;
+------+-------+------------+
| col  | col+0 | BIN(col+0) |
+------+-------+------------+
| a,c  |     5 | 101        |
+------+-------+------------+
1 row in set (0.00 sec)
  • a = 0b0001, c = 0b0101

データ型のストレージ要件

[https://dev.mysql.com/doc/refman/5.6/ja/storage-requirements.htmlembed:cite]

  • テーブルの内部表現の最大行サイズは 65,535 バイト
  • TEXTやBLOBは外出しされるので9-12バイトしか関与しない

    • 【補】PostgreSQLのTOAST(The Oversized-Attribute Storage Technique)みたいな