入力文字数を、フロントエンドとバックエンドで統一する方法

Question

前提

JavaScript・php・SQLで掲示板サイトを作成しているのですが、絵文字を含む文字数のカウントで苦慮しています。

実現したいこと

次の条件でコメント入力を可能にしたいです。

・絵文字を含む（書記素単位で）「最大500文字まで」にしたい
・カラムにはインデックスを貼る

（合字は気にしません。）

発生している問題

まずは、JavaScriptで500文字の検証は出来ました。
ライブラリ「grapheme-splitter」を使います。
https://github.com/orling/grapheme-splitter

そして、PHPでも出来ました。
関数grapheme_strlenを使います。
https://www.php.net/manual/ja/function.grapheme-strlen.php

しかし、SQLでは以下のように出来ません。

該当のソースコード・エラーメッセージ

SQLでは以下ソースコードとエラーメッセージになります。（実現したいのは500文字ですが、わかりやすくVARCHAR(10)にしています。）

-- ソースコード
CREATE TABLE test_table (
	 id       INT          UNSIGNED NOT NULL AUTO_INCREMENT
	,content  VARCHAR(10)  CHARACTER SET utf8mb4 NOT NULL
	,PRIMARY KEY (id)
	,INDEX idx_test_table_01 (content)    	
);

INSERT INTO test_table ( content )
VALUES ( 'abcdefghi🏴󠁧󠁢󠁥󠁮󠁧󠁿' ); -- 旗の絵文字のバイト数にご注意ください

-- エラーメッセージ
-- #1406 - Data too long for column 'content' at row 1

上記の'abcdefghi🏴󠁧󠁢󠁥󠁮󠁧󠁿'は、JavaScriptとPHPでは10になるのですが、SQLのVARCHAR(10)ではエラーになってしまうのです。

確認したこと

まず、DB接続はnew PDOで行っておりますが、そのcharsetはutf8mb4でした。

そして、SHOW VARIABLES LIKE '%char%';を実行し、character_set_serverはutf8mb4でした。

試したこと１ ( COLLATE 指定 )

次のALTER TABLEを実行しても変わりありませんでした。

ALTER TABLE test_table MODIFY content VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE test_table MODIFY content VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

試したこと２ ( CHAR_LENGTH でのカウント )

SQLだけで整合が取れないなら、SQLだけで文字数のカウントを行う次のCHAR_LENGTHというアプローチも考えられます。が、しかしこれは「最大500文字まで」を実現することができません。
ユーザーにとって（書記素単位では）🏴󠁧󠁢󠁥󠁮󠁧󠁿は１文字なのに、これではそういったカウントができないのです。

<?php
function getCharLength($str) {
  $mysqli = new MySQLi('db_host', 'db_user', 'db_pass', 'db_name');
  $mysqli->set_charset('utf8mb4');
  $sql = sprintf('SELECT CHAR_LENGTH("%s")', $mysqli->real_escape_string($str));
  $result = (int)$mysqli->query($sql)->fetch_column();
  $mysqli->close();
  return $result;
}

難しいポイント

難しいのは「最大500文字まで」という点です。

もし「最大10文字まで」ならJavaScriptとphpで10文字をカウントする検証を挟み、SQLはVARCHAR(100)にでもしておけば済む話です。もし🏴󠁧󠁢󠁥󠁮󠁧󠁿を10個入力されても問題ありません。

しかし「最大500文字まで」なので、もし🏴󠁧󠁢󠁥󠁮󠁧󠁿を500個入力されたとき、JavaScriptとphpでは問題なく500文字なのに、SQLでは768を超えてしまうという点です。（SQLでインデックスを貼るとVARCHAR(768)が限度）

ツールのバージョン

php: 8.1
MySQL: 5.7

Comments

[maisumakun]

> SQLでインデックスを貼るとVARCHAR(768)が限度

設定次第でその上限は外せるという情報もありましたが、そういう手段は取れないのでしょうか？

[nikuatsu]

いえ、そういう手段があれば是非知りたいです。

早速検索し次の３つの手段を見つけましたが、それぞれ微妙な印象です。
https://blog.e2info.co.jp/2017/04/17/mysqlのインデックスサイズに767byteまでしかつかえない/

１：絵文字を諦める
→ 諦めたくありません
２：191バイトにする
→ 768以上使いたいのに、191にしてどうするのか、理解ができません
３：innodb_large_prefix
→ レンタルサーバーのため権限なしです

もしmaisumakun様の方で他にお心当たりございましたらご教示いただけませんでしょうか。

[maisumakun]

あと、全文にインデックスを入れたいのはどんな事情でしょうか（プレフィックスインデックスでは不足する状況でしょうか）。

[nikuatsu]

検索したいという事情でインデックスを条件にしたのですが、プレフィックスインデックスというのを初めて知りました。アドバイスありがとうございます。

早速調べてみると以下見つけました。
http://hono-linux.seesaa.net/article/416490499.html

どうやらテストデータが必要なようなので、いくつかINSERTした上でまたコメントさせて頂ければと思います。

[maisumakun]

> 検索したいという事情でインデックスを条件にしたのですが

完全一致以外の検索をかけたいのであれば、全文検索インデックスのほうが適当かもしれません。

[nikuatsu]

なるほど。全文検索インデックスというのも良さそうに思えてきました。取り組んで比べてみたいと思います。ありがとうございます。

Answer 1

utf8mb4で処理する限りvarchar(10)レベルなら特に問題おきてないですね

create table test_table (
id int(10) unsigned primary key auto_increment,
content varchar(10) NOT NULL,
index(content)
) engine = innodb default character set utf8mb4 collate utf8mb4_general_ci;

INSERT INTO test_table ( content )
VALUES ( 'abcdefghi' ),( 'abcdefghi🏴󠁧󠁢󠁥󠁮󠁧󠁿' ),( 'abcdefghi🏴󠁧󠁢󠁥󠁮󠁧󠁿j' );

Comments

[nikuatsu]

ありがとうございます。妙ですね。そちらは質問のALTER TABLEで同じテーブルになると思いますし、とはいえyambejp様のご回答ならばと思ってそちらコピペし実行致しましたが、やはりData too longのエラーでした。何か設定があるのでしょうか。

[yambejp]

my.cnfの設定で[mysqld]にsql_mode=''を設定するとエラーは回避できるかもしれません
utf8mb4なので普通なら絵文字は1文字計算されるんですけどね

[yambejp]

なお、長い文字列をvarchar指定するのはあまり意味がありません
text型などに切り替えた方がよいでしょう。
文字数制限は生成列を利用すれば制限を設定できるかもしれません

[nikuatsu]

なるほど。sql_mode='' にしてしまうのはアリかもしれません。ありがとうございます。考えつきませんでした。
TEXTは遅そうという先入観があったのですが、検索するとあまり大きな差はでないとのことでしたのでこちらも検討させて頂きます。

[yambejp]

🏴󠁧󠁢󠁥󠁮󠁧󠁿はchar_lengthで5文字カウントですね
varchar(10)にすると2つしか保持できずvarchar(11)にすると3つ保持できるという
ちょっと特殊な状況ですね。
SQL側でこの手の絵文字を1文字カウントするのは難しそうです。
ミドルウェア側で調整し、DBでは多めにバッファを取って処理するのが限界かもしれません

もしくは「🏴」のようにエンティティ化して文字単位ではない工夫をするとか・・・

[nikuatsu]

ありがとうございます。多めにバッファという案が良さそうです。参考にさせて頂きます。