識別子の大文字と小文字の違い(2)

あの頃(何時)はまだMySQLに関する知識がなかった‥

識別子の大文字と小文字の違い - HHeLiBeXの日記正道編

で、MySQLでは、列名などの識別子はバッククォートで括るらしい(ということを、「show create table hoge」を実行して気づいたらしい)。
ということで、以前に実施した検証のMySQL版。
今回、以下のバージョンで検証。

MySQL 4.1
MySQL 5.5

MySQL版では、テーブル定義は次のようになる(ダブルクォートをバッククォートに置き換えて、BINARYオプションをつけただけ)。

-- 大文字・小文字、バッククォートで括る・括らない
CREATE TABLE hoge(
    id VARCHAR(8) BINARY,
    val1 INT,
    VAL2 INT,
    `val3` INT,
    `VAL4` INT
) ;
-- 試行する列名指定のパターン
INSERT INTO hoge(id) VALUES ('val1') ;
INSERT INTO hoge(id) VALUES ('val2') ;
INSERT INTO hoge(id) VALUES ('val3') ;
INSERT INTO hoge(id) VALUES ('val4') ;
INSERT INTO hoge(id) VALUES ('VAL1') ;
INSERT INTO hoge(id) VALUES ('VAL2') ;
INSERT INTO hoge(id) VALUES ('VAL3') ;
INSERT INTO hoge(id) VALUES ('VAL4') ;
INSERT INTO hoge(id) VALUES ('`val1`') ;
INSERT INTO hoge(id) VALUES ('`val2`') ;
INSERT INTO hoge(id) VALUES ('`val3`') ;
INSERT INTO hoge(id) VALUES ('`val4`') ;
INSERT INTO hoge(id) VALUES ('`VAL1`') ;
INSERT INTO hoge(id) VALUES ('`VAL2`') ;
INSERT INTO hoge(id) VALUES ('`VAL3`') ;
INSERT INTO hoge(id) VALUES ('`VAL4`') ;

で、試行(こちらも、ダブルクォートをバッククォートに置き換えて、BINARYオプションをつけたもの。さらに「my_log」という名前にした。MySQL 4.1で「CREATE TABLE log‥」がエラーになったための措置(「CREATE TABLE `log`‥」は通るので、何かのキーワードなのだろうな(保留(謎))))。

-- 試行結果の記録。
-- 指定した列が存在したら、テーブル my_log に列名指定のパターンが記録される。
CREATE TABLE my_log(id VARCHAR(8) BINARY, val INT) ;
INSERT INTO my_log(id, val) SELECT id, val1   FROM hoge WHERE id = 'val1' ;
INSERT INTO my_log(id, val) SELECT id, VAL1   FROM hoge WHERE id = 'VAL1' ;
INSERT INTO my_log(id, val) SELECT id, `val1` FROM hoge WHERE id = '`val1`' ;
INSERT INTO my_log(id, val) SELECT id, `VAL1` FROM hoge WHERE id = '`VAL1`' ;
INSERT INTO my_log(id, val) SELECT id, val2   FROM hoge WHERE id = 'val2' ;
INSERT INTO my_log(id, val) SELECT id, VAL2   FROM hoge WHERE id = 'VAL2' ;
INSERT INTO my_log(id, val) SELECT id, `val2` FROM hoge WHERE id = '`val2`' ;
INSERT INTO my_log(id, val) SELECT id, `VAL2` FROM hoge WHERE id = '`VAL2`' ;
INSERT INTO my_log(id, val) SELECT id, val3   FROM hoge WHERE id = 'val3' ;
INSERT INTO my_log(id, val) SELECT id, VAL3   FROM hoge WHERE id = 'VAL3' ;
INSERT INTO my_log(id, val) SELECT id, `val3` FROM hoge WHERE id = '`val3`' ;
INSERT INTO my_log(id, val) SELECT id, `VAL3` FROM hoge WHERE id = '`VAL3`' ;
INSERT INTO my_log(id, val) SELECT id, val4   FROM hoge WHERE id = 'val4' ;
INSERT INTO my_log(id, val) SELECT id, VAL4   FROM hoge WHERE id = 'VAL4' ;
INSERT INTO my_log(id, val) SELECT id, `val4` FROM hoge WHERE id = '`val4`' ;
INSERT INTO my_log(id, val) SELECT id, `VAL4` FROM hoge WHERE id = '`VAL4`' ;

-- テーブル my_log にあれば'O'、なければ'X'
SELECT hoge.id, 'O' AS result
    FROM hoge
    WHERE hoge.id IN (SELECT id FROM my_log)
UNION
SELECT hoge.id, 'X' AS result
    FROM hoge
    WHERE hoge.id NOT IN (SELECT id FROM my_log)
;

で、実行結果なのだが、全部'O'だけど一応ちゃんと表にしておく。

対象列	列名指定	MySQL 4.1	MySQL 5.5
val1	val1	O	O
	val2	O	O
	val3	O	O
	val4	O	O
VAL2	VAL1	O	O
	VAL2	O	O
	VAL3	O	O
	VAL4	O	O
`val3`	`val1`	O	O
	`val2`	O	O
	`val3`	O	O
	`val4`	O	O
`VAL4`	`VAL1`	O	O
	`VAL2`	O	O
	`VAL3`	O	O
	`VAL4`	O	O

さて、「show create table hoge」をしてみると、以下のような結果が返ってくる。(2者の違いは、単にデフォルトの文字エンコーディングが'utf8'か'utf8mb4'かだけ)

MySQL 4.1

CREATE TABLE `hoge` (
  `id` varchar(8) character set utf8 collate utf8_bin default NULL,
  `val1` int(11) default NULL,
  `VAL2` int(11) default NULL,
  `val3` int(11) default NULL,
  `VAL4` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

MySQL 5.5

CREATE TABLE `hoge` (
  `id` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `val1` int(11) DEFAULT NULL,
  `VAL2` int(11) DEFAULT NULL,
  `val3` int(11) DEFAULT NULL,
  `VAL4` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

また、MySQL 5.xでは、情報スキーマから列名一覧を取得できる。

SELECT TABLE_NAME, COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'hoge' ;

結果はこんな感じ。

	TABLE_NAME	COLUMN_NAME
1	hoge	id
2	hoge	val1
3	hoge	VAL2
4	hoge	val3
5	hoge	VAL4

と、以前検証したとおりの結果。(情報スキーマ-列名の取得 - HHeLiBeXの日記正道編)
そこで、「show create table INFORMATION_SCHEMA.COLUMNS」してみる。

CREATE TEMPORARY TABLE `COLUMNS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
      :
  (以下略)

COLUMN_NAME列などにBINARYオプションがついていないということは、やはり大文字小文字の区別をつけることは、MySQLではできそうにない。(実際、「CREATE TABLE foo(`val1` INT, `VAL1` INT)」するとエラーになる)
まぁ、大文字小文字だけが違う列が定義できても混乱の元になるだけなんだけど。