MySQL 5.7 で、全レコードの指定カラムにランダムなひらがなｎ文字を UPDATE したい

Question

実現したいこと

下記のように CREATE, INSERT したレコードがあります。
この nameカラムを、ランダムなひらがな10文字で UPDATE したいです。

CREATE TABLE `test_users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) DEFAULT ''
);

INSERT INTO `test_users` (`name`) VALUES ('aaa');

発生している問題・エラーメッセージ

phpMyAdmin の SQL タブに、後述する該当のソースコードの UPDATE をコピペし実行したものの、下記のエラーとなってしまいました。

MySQL のメッセージ: ドキュメント

#1366 - Incorrect string value: '\x800G0F0...' for column 'name' at row 11

該当のソースコード

こちらがエラーとなった UPDATE です。

UPDATE `test_users`
SET `name` = (
    SELECT GROUP_CONCAT(c SEPARATOR '')
    FROM (
        SELECT CHAR(CONV(HEX(FLOOR(12353 + RAND() * 83)), 16, 10)) AS c
        FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION 
              SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) tmp
    ) chars
);

試したこと

試したこと(1) カラムの確認

エラーを見ると '\x930u0O0...' の表示があるので文字コードのせいかと思いました。
そこで SHOW FULL COLUMNS FROM test_users; で確認しましたが、name カラムは utf8mb4_general_ci でした。

試したこと(2) 半角英文字の UPDATE

そしてひらがなでなく下記のような半角英文字なら無事 UPDATE できました。

UPDATE `test_users`
SET `name` = (
    SELECT GROUP_CONCAT(CHAR(FLOOR(97 + RAND() * 26)) SEPARATOR '')
    FROM (
        SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION 
              SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
    ) tmp
);

試したこと(3) ひらがなの INSERT

また、ひらがなでも下記 INSERT なら無事できました。

INSERT INTO `test_users` (`name`) VALUES ('あああ');

試したこと(4) 文字コードを再確認

SHOW VARIABLES LIKE 'character_set%'; を実行すると、結果は下記でした。

character_set_client: utf8mb4
character_set_connection: utf8mb4
character_set_database: utf8mb4
character_set_server: utf8mb4

補足

バージョンは SELECT VERSION(); して 5.7.27-log の表示でした。

Answer 1

ひらがなのリストを作っておいてfunctionで処理すれば楽でしょう

drop function if exists rnd_hiragana ;
delimiter //
create function rnd_hiragana(input INT)
returns varchar(100)  deterministic
begin
declare org varchar(100);
declare ret varchar(100);
set org='あいうえお';
set ret='';
while char_length(ret) < input do
set ret=concat(ret,substring(org,ceil(rand() * char_length(org)),1));
end while;
return ret;
end ;
//
delimiter ;

test

create table tbl (id int primary key auto_increment,val varchar(20));
insert into tbl(val) values('あああ'),('いいい'),('ううう'),('えええ'),('おおお');
update tbl set val=rnd_hiragana(10);

Comments

[munekun]

驚きでした。MySQL も php や JavaScript などと同様に function を定義できるのですね。
そしてそれを set で呼ぶと・・。へぇぇ、大変勉強になりました。

無事問題も解決しました。ありがとうございます。

Answer 2

unicodeのコードポイントの数値と、UTF-8で符号化されたバイト列は別物です。

UTF-8の文字コード表とかで確認すると分かりやすいのですが
平仮名は3バイトの 0xE38180～0xE38296辺りで不連続な範囲に定義されています。

mysql では以下のように確認できます

SELECT HEX('ぁ'); -- => E38181
SELECT HEX('あ'); -- => E38182
SELECT HEX('ぃ'); -- => E38183
SELECT HEX('い'); -- => E38184
-- 略
SELECT HEX('ゖ'); -- => E38294

なので、UTF-8の数値ベースでのランダム生成はめんどくさいです。

代替案としてutf32とかならコードポイントで指定できて手っ取り早いと思われます。
こちらは連続範囲なのでランダム生成もしやすいです

SELECT HEX(CONVERT('ぁ' USING utf32)); -- => 00003041 ... 0x3041 = 12353
SELECT HEX(CONVERT('あ' USING utf32)); -- => 00003042
SELECT HEX(CONVERT('ぃ' USING utf32)); -- => 00003043
SELECT HEX(CONVERT('い' USING utf32)); -- => 00003044
-- 略
SELECT HEX(CONVERT('ゖ' USING utf32)); -- => 00003096

コードポイントの数値ベース≒utf32で文字化させると

SELECT CHAR(12353 USING utf32); -- => ぁ

SELECT CHAR(FLOOR(12353 + RAND() * 86) USING utf32); -- => ランダムなひらがな１文字 ※*83でも良い

ちなみに半角英文字だとうまくいくというのは
asciiコード範囲（1バイトというか7bitまで）では
unicodeのコードポイント・UTF-8の符号化バイト列のいずれとも互換性を持つように設計されているからです。

Comments

[munekun]

お詳しい方ばかりですね・・
どういう理屈でできないのかわからなかったので、pecmm様のご回答も大変参考になりました。
ありがとうございます。