アーカイブ

‘MySQL’ タグのついている投稿

[MySQL] NOT NULLかつDEFAULTなしの列を追加した場合、データタイプデフォルト値が設定される。

2009 年 9 月 15 日 コメントはありません

MySQL(5.0系)で、すでにレコードが存在するテーブルにNOT NULLかつDEFAULTなしの列を追加してもエラーにならなかった。きちんと理解していないので調べておく。

以下のテーブルを作成し、レコードを追加しておく。

mysql> CREATE TABLE users (id INTEGER PRIMARY KEY);
mysql> DESC users;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    |                | 
+-------+---------+------+-----+---------+----------------+
mysql> INSERT INTO users VALUES(1);

NOT NULLでDEFAULTなしの列を追加してみる。

mysql> ALTER TABLE users ADD (name VARCHAR(255) NOT NULL, 
age INTEGER NOT NULL, created_at DATETIME NOT NULL);

エラーにならず、以下の値が設定された。

mysql> SELECT * FROM USERS;
+----+------+-----+---------------------+
| id | name | age | created_at          |
+----+------+-----+---------------------+
|  1 |      |   0 | 0000-00-00 00:00:00 | 
+----+------+-----+---------------------+

どうやら「データタイプデフォルト値」が設定されるもよう。
これは「ストリクトSQLモード」が有効な場合でも同じ結果となる。試してみる。

-- いったん列を削除
mysql> ALTER TABLE users DROP name;
mysql> ALTER TABLE users DROP age;
mysql> ALTER TABLE users DROP created_at;
 
-- ストリクトSQLモードを有効に
mysql> SET sql_mode = 'STRICT_ALL_TABLES';
mysql> SELECT @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES | 
+-------------------+
 
-- ストリクトSQLモードでもエラーにならない
mysql> ALTER TABLE users ADD (name VARCHAR(255) NOT NULL, 
age INTEGER NOT NULL, created_at DATETIME NOT NULL);
mysql> SELECT * FROM USERS;
+----+------+-----+---------------------+
| id | name | age | created_at          |
+----+------+-----+---------------------+
|  1 |      |   0 | 0000-00-00 00:00:00 | 
+----+------+-----+---------------------+

せめてストリクトSQLモードが有効な場合にはエラーにしてほしいですね。要注意です。

参照ドキュメント:

カテゴリー: 未分類 タグ:

[MySQL] int(11)の11は最大桁数ではない

2009 年 9 月 1 日 コメント 1 件

危なかった。勘違いしてた。
int(11)の「11」って表示桁数を揃えるためのものであって、格納できるのはあくまで4バイトなんですね。
参照: 本家ドキュメント

試してみる。

mysql> create table int_test (id int(11) primary key);
Query OK, 0 rows affected (0.04 sec)
 
mysql> desc int_test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       | 
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
 
mysql> insert into int_test values(2147483647);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into int_test values(2147483648);
ERROR 1062 (23000): Duplicate entry '2147483647' for key 1
カテゴリー: 未分類 タグ:

MySQLのvarcharは、最大サイズ65,535(バージョン5.0.3以降)

2009 年 3 月 24 日 コメントはありません

いろいろ勘違いしていたので整理。

  • varchar で指定するサイズは、バイト数ではなく文字数。
    • varchar(5)なフィールドに「あいうえお」を格納できる。
      (クライアント文字コードが正しく設定されている前提の話)
  • varcharの最大サイズは65,535。
    • ただし行にも最大サイズ(65,535「バイト」)があり、これに収まる限り。
  • blob、text型については別格納となるので、行サイズとしてはポインタ分のみ消費。

参照リファレンスは以下

カテゴリー: 未分類 タグ:

MySQLのGROUP BYは、寛容すぎて気持ちが悪い。

2009 年 2 月 25 日 コメントはありません

以前の職場ではOracleばかり使っていたのですが、MySQLを使うようになってちょっとカルチャーショック、というお話。

以下のようなテーブルがあってですよ、

mysql> SELECT table_a.* FROM table_a;
+-------+-------+-------+
| col_a | col_b | col_c |
+-------+-------+-------+
|     1 |     1 |     1 | 
|     2 |     1 |     2 | 
|     3 |     2 |     2 | 
|     4 |     2 |     1 | 
+-------+-------+-------+
4 rows in set (0.00 sec)

以下のように、GROUP BYに含まれない列でもSELECTできちゃうんですね。

mysql> SELECT col_b, col_c FROM table_a GROUP BY col_b;
+-------+-------+
| col_b | col_c |
+-------+-------+
|     1 |     1 | 
|     2 |     2 | 
+-------+-------+
2 rows in set (0.00 sec)

col_cの値はどれがSELECTされるのだろう…。物理的な先頭行の値?

Oracleだとたしか構文エラーになったよなーなんて思っていたのですが、ちゃんと「MySQLによる拡張です」って書いてありました。
本家ドキュメント:MySQL extends the use of GROUP BY…

前述のようなケースは「やっちゃだめ」とも書いてありますね。

Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group.

さらに環境変数 sql_modeONLY_FULL_GROUP_BY を設定すれば、この拡張は抑制されるみたい。

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT col_b, col_c FROM table_a GROUP BY col_b;
ERROR 1055 (42000): 'database_a.table_a.col_c' isn't in GROUP BY

あいまいなSQLを未然に防止するためには、設定しておいた方がいい気がする…。

カテゴリー: 未分類 タグ: