なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQLの文字列型の扱い

先日、この記事が公開されていたので

gihyo.jp



アレ?って思ってドキュメント見直して、手元の環境でも実行してみました。


確かに「その通り」だったのだけど、ドキュメントに書いてあるとおりのこと、かつ、「その一部」の話だなーとおもったので、もうちょい補足するつもりで。


「その(公式ドキュメントの)一部」、っていうのは、記事中は「取り出し時のサイズ」の話だけをしていて、「消費するストレージサイズ」の話には言及していない、ということ。
そして、一読した段階ではそのことに気付かず、混同したままだったので、アレ?って思ったわけです。

charは固定で後ろスペース埋め、varcharは可変で余剰切り捨て、って覚えるよね

ですが、これは「格納時の、消費ストレージサイズ」の話。


MySQLの「取り出し」の時は、記事中にある通り、「charは後ろのスペースを切り捨て」「varhcarは、格納した通りに取り出し」になります。


さらに、MySQLの場合、
・varcharでは、プリフィックスで1byte余計に消費しますので、varchar(4)は4+1で5byte消費します。
・charでは、行フォーマットがCOMPACT(MYSQL5.6デフォルト)/DYNAMIC(MySQL5.7デフォルト)の場合、文字コードと格納文字列数によって、実際の消費スペースが可変になります。
 具体的には、以下の記事参照。
atsuizo.hatenadiary.jp


ごちゃごちゃ言わずに実験結果

関数lengthはbyte数。char_lengthは文字列数です。
なお、lengthはbyte数、と言っても、プリフィックス分付きのストレージサイズは取れません。あくまで格納文字列に対するものだけです。

mysql> create database test_chr;
Query OK, 1 row affected (0.00 sec)

mysql> use test_chr
Database changed

mysql> show create database test_chr\G
*************************** 1. row ***************************
       Database: test_chr
Create Database: CREATE DATABASE `test_chr` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

mysql> create table chr_tbl (col1 int,col2 char(4),col3 varchar(4));
Query OK, 0 rows affected (0.09 sec)

mysql> show create table chr_tbl\G
*************************** 1. row ***************************
       Table: chr_tbl
Create Table: CREATE TABLE `chr_tbl` (
  `col1` int(11) DEFAULT NULL,
  `col2` char(4) DEFAULT NULL,
  `col3` varchar(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into chr_tbl values (1,'a b ','a b '),(2,'a b','a b'),(3,'ab  ','ab  '),(4,'ab ','ab '),(5,'','');
Query OK, 5 rows affected (0.09 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select col1,concat('(',col2,')') as chr,length(col2),char_length(col2),concat('(',col3,')') as vch,length(col3),char_length(col3) from chr_tbl;
+------+-------+--------------+-------------------+--------+--------------+-------------------+
| col1 | chr   | length(col2) | char_length(col2) | vch    | length(col3) | char_length(col3) |
+------+-------+--------------+-------------------+--------+--------------+-------------------+
|    1 | (a b) |            3 |                 3 | (a b ) |            4 |                 4 |
|    2 | (a b) |            3 |                 3 | (a b)  |            3 |                 3 |
|    3 | (ab)  |            2 |                 2 | (ab  ) |            4 |                 4 |
|    4 | (ab)  |            2 |                 2 | (ab )  |            3 |                 3 |
|    5 | ()    |            0 |                 0 | ()     |            0 |                 0 |
+------+-------+--------------+-------------------+--------+--------------+-------------------+
5 rows in set (0.00 sec)


なお、日本語、というかutf8mb4環境の場合、こんな感じになります。

mysql> CREATE TABLE `chr_tbl_utf8mb4` (
    ->   `col1` int(11) DEFAULT NULL,
    ->   `col2` char(4) DEFAULT NULL,
    ->   `col3` varchar(4) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into chr_tbl_utf8mb4 values (1,'あ い','あ い '),(2,'あ い','あ い'),(3,'あい  ','あい  '),(4,'あい ','あい '),(5,'',''),(6,' ',' ');
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select col1,concat('(',col2,')') as chr,length(col2),char_length(col2),concat('(',col3,')') as vch,length(col3),char_length(col3) from chr_tbl_utf8mb4;
+------+-----------+--------------+-------------------+------------+--------------+-------------------+
| col1 | chr       | length(col2) | char_length(col2) | vch        | length(col3) | char_length(col3) |
+------+-----------+--------------+-------------------+------------+--------------+-------------------+
|    1 | (あ い)   |            7 |                 3 | (あ い )   |            8 |                 4 |
|    2 | (あ い)   |            7 |                 3 | (あ い)    |            7 |                 3 |
|    3 | (あい)    |            6 |                 2 | (あい  )   |            8 |                 4 |
|    4 | (あい)    |            6 |                 2 | (あい )    |            7 |                 3 |
|    5 | ()        |            0 |                 0 | ()         |            0 |                 0 |
|    6 | ( )      |            3 |                 1 | ( )       |            3 |                 1 |
+------+-----------+--------------+-------------------+------------+--------------+-------------------+
6 rows in set (0.00 sec)

utf8mb4でも、実際に4byte文字が来ないかぎり、1文字4byteにはなりません。多くの日本語文字は3byteですね。
ただし、元記事にもある通り、計算上は1文字4byteで計算されて、1行あたりのサイズ上限が計算されますので、そこは要注意です。


まとめ

  • char,varcharで、取り出し時の挙動が違う。
  • char,varcharで、消費ストレージサイズが違う。
  • 「取り出し時」と「消費ストレージサイズ」のスペースの扱いは別。
  • 消費ストレージサイズは、ROW FORMATによっても変わる。


こちらからは以上です。

やさしく学べるMySQL運用・管理入門【5.7対応】

やさしく学べるMySQL運用・管理入門【5.7対応】