MySQLのTEXT型とVARCHAR型の文字の数え方を観察する

はじめに

イタンジ株式会社の細川です。MySQLを利用したWEBサービスを開発しています。最近はトポロジカルソートでボジョレーヌーボーの序列が解けるのか気になっています。 今回は、TEXT型とVARCHAR型の格納できる文字数について調べたので書いていきます。

背景

新しいカラムを追加する時に格納する文字数が多くなりそうなのでTEXT型にしようと思ったことがあります。カラムに指定したデータ型より超過した文字数の文字列を格納するとき、超過分は切り捨てられてエラーにもならなかった経験をおぼろげに思い出していました。それはまずいなぁ、と思っていたので、エンドユーザーに文字数超過の場合にエラーを伝えるようにしようと考えました。ふとTEXTで格納できる文字数って絵文字を含んでも指定通りなのだっけと疑問が湧いてきました。おもむろにドキュメントを読み、実際に動かして観察してみて、「なるほど、そうだったのか」という気づきがあったことがこのブログを書こうと思った動機です。

ドキュメントを読む

MySQLのドキュメントには以下のように記載があります。

VARCHAR(M) M はカラムの最大長を文字数で表します。

TEXT[(M)] 最大長が 65,535 (2**16 − 1) 文字の TEXT カラム。 値にマルチバイト文字が含まれる場合、有効な最大長は少なくなります。

https://dev.mysql.com/doc/refman/8.0/ja/string-type-syntax.html

いきなり結論なのですが、TEXT型は値にマルチバイト文字が含まれる場合は有効な最大長は少なくなります。

"少なくなる"、とは書いてはいるものの、

  • どれだけ少なくなるのか?
  • マルチバイト文字とは4バイト文字を指しているのか?(あるいは2バイト、3バイトでも違いがでるのか)

これらの疑問が思い浮かんでいました。なぜならアプリケーション側で文字数超過エラーを伝えるために文字数超過判定が正しくできなければならないからです。

厳密な SQL モード

ドキュメントを読んでいると以下の記述が目につきました。

厳密な SQL モードが有効でない場合に、BLOB または TEXT カラムにその最大長を超える値を割り当てると、その値はカラムの最大長に合わせて切り捨てられ、警告メッセージが表示されます。

https://dev.mysql.com/doc/refman/8.0/ja/blob.html

厳密なSQLモードは5.7からデフォルトとなっているようでして、これが有効だと最大長を超える値はエラーとなります。ですので、冒頭の超過文字が切り捨てられた経験は厳密なSQLモードでないために起きたものだったろうと推測します。厳密なSQLモードを利用しているときは内容が切り捨てられることがないので安心ですね。

動かして観察する

何文字で文字が切り捨てられるのかを動かしてみて観察してみようと思いました。

$ docker run --rm -p 3306:3306 --env MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:8
#!/usr/bin/env ruby

require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem 'activerecord', require: 'active_record'
  gem 'mysql2'
  gem 'rspec', require: %w[rspec/autorun]
end

config = {
  encoding: 'utf8mb4',
  charset: 'utf8mb4',
  adapter: 'mysql2',
  database: 'text-count-study',
  host: '127.0.0.1',
  username: 'root',
  password: '',
  variables: { sql_mode: '' } # 文字数超過でエラーにしない
}

ActiveRecord::Base.then do |base|
  base.establish_connection(config.except(:database))
  base.connection.execute("CREATE DATABASE IF NOT EXISTS `#{config[:database]}`")
  base.connection.disconnect!
  base.establish_connection(config)
end

RSpec.configure { _1.formatter = :documentation }
RSpec.describe 'TEXT型とVARCHAR型で格納できる文字数について' do
  dummy_table_name = SecureRandom.urlsafe_base64

  before do
    ActiveRecord::Base.connection.create_table(dummy_table_name) do |t|
      t.string :string_col, null: true, limit: 65535
      t.text :text_col, null: true # 最大長が 65535 (2**16 − 1) 文字の TEXT カラム
    end

    stub_const(
      'TestClass',
      Class.new(ActiveRecord::Base) { self.table_name = dummy_table_name }
    )
  end

  after { ActiveRecord::Base.connection.drop_table(dummy_table_name) }

  shared_examples 'check' do |col, char|
    context "#{char.bytesize}バイト文字(#{char.inspect})を65535文字して挿入すると" do
      let(:model) { TestClass.create!(col => char * 65535) }

      it 'データベースに格納された文字は65535文字?' do
        expect(model.reload.__send__(col).length).to eq 65535
      end
    end
  end

  describe 'VARCHAR(65535)型カラムを対象とするとき' do
    include_examples 'check', :string_col, 'a'
    include_examples 'check', :string_col, '©'
    include_examples 'check', :string_col, ''
    include_examples 'check', :string_col, '🍺'
  end

  describe 'TEXT型カラムを対象とするとき' do
    include_examples 'check', :text_col, 'a'
    include_examples 'check', :text_col, '©'
    include_examples 'check', :text_col, ''
    include_examples 'check', :text_col, '🍺'
  end
end

exit 0

上記のスクリプトはTEXT型とVARCHAR(65535)型のカラムを持つレコードをActiveRecordを利用してそれぞれのカラムに1バイト、2バイト、3バイト、4バイトの文字を65535文字で挿入し、再ロードしたときに何文字取り出せるかを表したものです。(挿入時点でエラーとしないために厳密なSQLモードを含めていません。)

実行結果は以下の通りでした。

TEXT型とVARCHAR型で格納できる文字数について
  VARCHAR(65535)型カラムを対象とするとき
    1バイト文字("a")を65535文字して挿入すると
      データベースに格納された文字は65535文字?
    2バイト文字("©")を65535文字して挿入すると
      データベースに格納された文字は65535文字?
    3バイト文字("あ")を65535文字して挿入すると
      データベースに格納された文字は65535文字?
    4バイト文字("🍺")を65535文字して挿入すると
      データベースに格納された文字は65535文字?
  TEXT型カラムを対象とするとき
    1バイト文字("a")を65535文字して挿入すると
      データベースに格納された文字は65535文字?
    2バイト文字("©")を65535文字して挿入すると
      データベースに格納された文字は65535文字? (FAILED - 1)
    3バイト文字("あ")を65535文字して挿入すると
      データベースに格納された文字は65535文字? (FAILED - 2)
    4バイト文字("🍺")を65535文字して挿入すると
      データベースに格納された文字は65535文字? (FAILED - 3)

Failures:

  1) TEXT型とVARCHAR型で格納できる文字数について TEXT型カラムを対象とするとき 2バイト文字("©")を65535文字して挿入すると データベースに格納された文字は65535文字?
     Failure/Error: expect(model.reload.__send__(col).length).to eq 65535
     
       expected: 65535
            got: 32767
     
       (compared using ==)
     Shared Example Group: "check" called from text_count_in_mysql.rb:67
     # text_count_in_mysql.rb:53:in `block (4 levels) in <main>'

  2) TEXT型とVARCHAR型で格納できる文字数について TEXT型カラムを対象とするとき 3バイト文字("あ")を65535文字して挿入すると データベースに格納された文字は65535文字?
     Failure/Error: expect(model.reload.__send__(col).length).to eq 65535
     
       expected: 65535
            got: 21845
     
       (compared using ==)
     Shared Example Group: "check" called from text_count_in_mysql.rb:68
     # text_count_in_mysql.rb:53:in `block (4 levels) in <main>'

  3) TEXT型とVARCHAR型で格納できる文字数について TEXT型カラムを対象とするとき 4バイト文字("🍺")を65535文字して挿入すると データベースに格納された文字は65535文字?
     Failure/Error: expect(model.reload.__send__(col).length).to eq 65535
     
       expected: 65535
            got: 16383
     
       (compared using ==)
     Shared Example Group: "check" called from text_count_in_mysql.rb:69
     # text_count_in_mysql.rb:53:in `block (4 levels) in <main>'

よってこの観察から

  • TEXT型カラムに 2バイト文字を65535文字入力した場合 取り出せるのは 32767文字
  • TEXT型カラムに 3バイト文字を65535文字入力した場合 取り出せるのは 21845文字
  • TEXT型カラムに 4バイト文字を65535文字入力した場合 取り出せるのは 16383文字

ということが読み取れ

  • 2バイト文字: 32767 * 2 = 65534
  • 3バイト文字: 21845 * 3 = 65535
  • 4バイト文字: 16383 * 4 = 65532

バイトサイズで65535を超えない値まで読み出すことができることがわかります。

つまり

  • VARCHAR(65535)型は 文字数で数えて65535まで 記録する
  • TEXT型は バイト数で数えて65535まで 記録する

ということであると理解しました。

ActiveRecordのLengthValidatorはvalue.lengthかvalue.to_s.lengthを利用1しています。 検証対象がTEXT型カラムだったとき、入力された文字列によってはアプリケーションでは合格してしまうのにMySQLで例外か切り捨てが起きうることと推測できます。TEXT型カラムを利用する場合は注意してみた方が良いという気づきになりました。

終わりに

今回は、TEXT型とVARCHAR型で格納できる文字数について調べました。 この記事をご覧の方の開発の一助になれば幸いです。それでは!