MySQLの文字列型の扱い
先日、この記事が公開されていたので
アレ?って思ってドキュメント見直して、手元の環境でも実行してみました。
確かに「その通り」だったのだけど、ドキュメントに書いてあるとおりのこと、かつ、「その一部」の話だなーとおもったので、もうちょい補足するつもりで。
「その(公式ドキュメントの)一部」、っていうのは、記事中は「取り出し時のサイズ」の話だけをしていて、「消費するストレージサイズ」の話には言及していない、ということ。
そして、一読した段階ではそのことに気付かず、混同したままだったので、アレ?って思ったわけです。
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によっても変わる。
こちらからは以上です。
- 作者: 梶山隆輔,山崎由章
- 出版社/メーカー: インプレス
- 発売日: 2016/12/15
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る