はじめに
新人研修にてLaravelを使っている時のお話です。
テーブル設計を行う時にデータ型とサイズを決めなければなりませんでした。
その時に、例えば、INTの(X)のXは何を表しているのか正確に理解していなかったので、この機会に使用頻度の高いCHAR型やVARCHAR型などの文字列を格納するデータ型も含め、調べたいと思いました。
この記事を読み終えたあなたが適切にデータ型やサイズを決めることができることをこの記事の目標にしたいと思います。
ちなみにQiita初投稿です(なんか緊張する...)。
検証環境
- macOS Catalina ver 10.15.7
- Docker ver 20.10.5
- docker-compose ver 1.29.0
- Laravel ver 7.30.4
- MySQL ver 8.20.3
内容
「ユニコード(Unicode)とは」
その前に、まず「文字コード」とは何なのか
- 文字に割り当てられた数字のこと。
MySQLでよくみる「utf8」と「utf8mb4」
- 「UTF-8」：文字コードであり、1文字を1~4バイトで表現する。
- 「utf8」と「utf8mb4」：MySQLにおけるUTF-8。
- UTF-8は1～4バイトで文字を表現するが、MySQLでは3バイトの文字までしか扱えないため、4バイトの文字は使用できない。
- ちなみに、2バイト以上で表現する文字(1バイトでは表現できない文字)を「マルチバイト文字」という。
- 「utf8mb4」
- そのため、4バイトに該当する文字を扱えるようにしたのがutf8mb4である。
- 4バイトの文字にはどのような文字があるのか。
- 絵文字。
- utf8に4バイトである絵文字を挿入した場合。
- エラーや「????」として挿入される。
では、「ユニコード(Unicode)」とは何なのか
- 「『文字』と『文字に割り当てた番号』の対応表」である「符号化文字集合」と呼ばれるものの一つ。
- 前提としてコンピュータは「0」と「1」しか理解できない。
- そのため「あ」は「xxxx」番、「い」は「yyyy」というように「文字と数字の対応表」がある。
- その対応表には種類があり、その一つが「ユニコード」である、他にはASCIIコードなどがある。
- 「コンピュータで文字を表現する」とは二つの表を対応させた上で成立している。
- 『文字』と『文字に割り当てた番号』の対応表=「符号化文字集合」
- 『文字に割り当てた番号』と『実際にコンピュータが扱う数字』の対応表=「文字符号化方式」
- 例)「あ」-「P-1」の対応表(符号化文字集合)と「P-1」-「00000001」の対応表(文字符号化方式) → 「あ」 - 「00000001」
- 「文字符号化方式」とは何なのか？
- 文字コードのこと。例えば「UTF-8」は、符号化文字集合としてユニコードを使うときの文字符号化方式のひとつである。
MySQLで文字コードを確認する時の「show variables like "chara%";」
- 参考サイトの「mysqlで文字コードをUTF-8にセットする」を参照してください。
「CHARとVARCHARの違いは何なのか」、「VARCHAR(X)の『X』は何を表しているのか」
CHARとVARCHARの違い
- CHARの特徴：固定長文字列
- CHAR(X)のXは「最大文字数」を表す。
- Xは0~255の値をとり、省略時は１。
- 最大文字数10とし、6文字の値を格納したときの挙動
- 常に最大文字数で値が格納されるため、不足分の４文字(10-6)はスペースで埋められる。
- 値の取得時は、6文字であり、取得に関しては影響しない。
- VARCHARの特徴：可変長文字列
- VARCHAR(X)はMySQLに関しては、バージョンによってXの意味は異なる。
- 4.1以前(10年以上前)はXは「バイト数」を意味し、4.1以降はユニコード等の実装に伴い、マルチバイトも1文字とカウントし、Xは文字数を表す。
- 最大行サイズは65,535バイト。
- utf8を使用している場合は、1文字が最大3バイトになり、VARCHARに登録できる文字数は最大21,844文字(65535/3 - 1)となる。
- utf8mb4を仕様している場合は、1文字が最大4バイトになり、VARCHARに登録できる文字数は最大16,382文字(65535/4 - 1)となる。
- では、「1バイトである『a』は16382文字以上挿入可能なのか」
- できない。1文字が4バイトより小さくても、16382文字までしか挿入できない。
- 注意点
- テーブル内の各カラムで指定した合計文字数に注意する
- The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
- utf8mb4を使用している場合は、16382文字を一つのカラムで使用した場合、他で文字列型のカラムを設けることは不可能。
- テーブル内の各カラムで指定した合計文字数に注意する
多くの記事でVARCHAR(X)はバイト数だとみかけるが、以下の関数を使って検証していただければ、「X」が何を表しているのかわかります(ver 4.1以降)。
- 以下、PHPの関数です。
- str_repeat関数
- 文字を反復させる時に使う。
- strlen関数
- 文字のバイト数を取得。
- str_repeat関数
※今回の記事では触れないが、文字列を格納するTEXT型などもあリます。
整数型について
INT(11)の「11」は何を表しているのか
- ()内の数値はサイズ指定ではなくて、「ZEROFILL」オプションを指定した際の表示幅指定=桁数である。
- 「バージョンXX以前は、バイト数だった」などありましたら、ご指摘お願いします。
- 桁数を揃えて見やすくすることが目的である(私の予想)。
- ちなみにLaravelのmigrationはZEROFILL対応してないらしい。
- 直接SQLで書くしかないそうです。
- では、データサイズは何で決まるのか。
- データサイズはデータ型できまる。
- tinyint
- smallint
- midiumint
- int
- bigint
- データサイズはデータ型できまる。
- 5つの整数型には「UNSIGNED」と「ZEROFILL」二つのオプションがある。
- 「UNSIGNED」は「符号あり」すなわちマイナスの値を格納できないようにする。
- 「ZEROFILL」はカッコ内で指定した数値の桁数になるようにゼロ埋め処理が実施される。
- 「ZEROFILL」を設定した場合、自動的に「UNSIGNED」オプションも付与される。
- 100桁で表示したいなんてことはまずないが、桁数を100に指定することはできる。
- 挙動
- 11未満の場合は、不足分0がつく。123の場合、残り8桁不足しているので「00000000123」となる。
- 桁数が超えるとどうなるか？
- 問題ない。あくまで桁数である。
- 例：int(5)のカラムにintの符号なし最大値4294967295を入れる。
- もちろん桁数超えて、挿入可能である。
さいごに
いかがだったでしょうか。
あなたが初学者であるなら、お力になれたら嬉しいです。ベテランの方でしたら、初学者がどのようなところで理解に苦しむのかがわかっていただければ幸いです。
参考サイト
- 「『UTF-8』『utf8』『utf8mb4』は何が違うの」
- 「varchar(255)で格納できる文字数」
- 「mysqlで文字コードをUTF-8にセットする」
- 「文字コードUTF-8とUTF-8mb4の違い MySQLでは絵文字の扱いに注意」
- 「符号化文字集合とは」
- 「MySQLで利用可能な整数型について解説！int(11)のカッコに設定された数値って何？」
- 「整数型(TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT)」
- 「LaravelでMySQLのZEROFILLをmigrationで実行する方法」