mariadb utf8mb4 with dynamic column & procedure

Question

I have two questions for mariadb utf8mb4 with dynamic column.

Above all, I use mariadb version 10.0 and connect by jdbc. For saving emoji characters, I modified mariadb as follow that,

1. Edited in /etc/my.cnf

   [mysqld]
   character-set-server = utf8mb4
   collation-server = utf8mb4_unicode_ci

2. Edited DB Table Charset.

    CREATE TABLE `MEMBER` (
      `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `regdate` datetime DEFAULT NULL,
      `sso_json` blob,
      ..(skip)..
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

3. Remove characterEncoding parameter from JDBC String

   deleted particle : characterEncoding=utf-8

So, It's perfect, emoji character was saved in a varchar column ㅁccurately. But not in a dynamic column. In both Ad-Hoc Query and procedure, column_create() save question mark instead of emoji.

As follow, procedure sample.

CREATE DEFINER=`sample`@`%` PROCEDURE `SP_INSERT`(
    inName varchar(500) CHARACTER SET utf8mb4
)
BEGIN

    SET @pSql = CONCAT( ' INSERT INTO SAMPLE_TBL ( '
                      , '   name, sso_json '
                      , ' ) VALUES ( '
                      , '   ?, COLUMN_CREATE(?, ?) '
                      , ' ) '
                      );


    -- variables bind
    SET @pName = inName;
    SET @pKey = 'title';        

    -- prepare stmt
    PREPARE pstmt FROM @pSql;
    EXECUTE pstmt USING @pName, @pKey, @pName;

END

Procedure Result : {'title', '?????'}.

And In a Ad-Hoc query,

set names utf8mb4 collate 'utf8mb4_unicode_ci';
select 'test????????????????', column_json(column_create('name','test????????????????'));

Result : test????????????????' || {\"name\":\"test????\"}

result column is accurately but column_json no.

set names utf8;
select 'test????????????????', column_json(column_create('name','test????????????????'));

Result : test???????????????? || {\"name\":\"test????????????????\"}

I don't know why. Help me, please.

Answer 1

sso_json blob acquires the table's DEFAULT CHARACTER SET utf8; you need utf8mb4 for Emoji, as you did with name.