MySQL SELECT 和 CREATE 给出不同的结果 : Character respectively hexadecimal

Question

我有一张表，其中包含不同时间点 t 的人员 (id) 和一个特征 (var0)。在某些时间点，该特征缺失，我想用以前的值来填补空白。下面是一个表格示例:

+---+---+----+             +----+---+------+------+------------------+
|id | t |var0|             | id | t | var0 | var1 | @prev_id   := id |
+---+---+----+             +----+---+------+------+------------------+
| 1 | 1 | a  |             |  1 | 1 | a    | a    |                1 |
| 1 | 3 | \N |             |  1 | 3 | \N   | a    |                1 |
| 1 | 7 | \N |             |  1 | 7 | \N   | a    |                1 |
| 1 | 8 | b  |             |  1 | 8 | b    | b    |                1 |
| 1 | 9 | \N |             |  1 | 9 | \N   | b    |                1 |
| 2 | 2 | \N |             |  2 | 2 | \N   | \N   |                2 |
| 2 | 4 | u  |             |  2 | 4 | u    | u    |                2 |
| 2 | 5 | u  |             |  2 | 5 | u    | u    |                2 |
| 2 | 6 | \N |             |  2 | 6 | \N   | u    |                2 |
| 2 | 7 | \N |             |  2 | 7 | \N   | u    |                2 |
| 2 | 8 | v  |             |  2 | 8 | v    | v    |                2 |
| 2 | 9 | \N |             |  2 | 9 | \N   | v    |                2 |
+---+---+----+             +----+---+------+------+------------------+

左表是原始的x1表，右表是请求的结果。下面是获取结果的代码:

DROP TABLE IF EXISTS x1;
CREATE TABLE test01.x1 (
  id   INTEGER
, t    INTEGER
, var0 CHAR(1)
) ENGINE = InnoDB 
DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci
;

INSERT INTO test01.x1(id,t,var0) VALUES
( 1,1,'a' )
,(1,3,NULL)
,(1,7,NULL)
,(1,8,'b' )
,(1,9,NULL)
,(2,2,NULL)
,(2,4,'u' )
,(2,5,'u' )
,(2,6,NULL)
,(2,7,NULL)
,(2,8,'v' )
,(2,9,NULL)
;

-- DROP TABLE IF EXISTS test01.x2;
-- CREATE TABLE test01.x2
SELECT id, t
       , var0
       , @prev_var0 := IF(id = @prev_id AND var0 IS NULL AND @prev_var0 IS NOT NULL 
                       , @prev_var0
                       , var0 
                       ) var1
       , @prev_id   := id
FROM test01.x1, (SELECT @prev_id    := NULL
                    ,@prev_var0 := NULL
        ) init
ORDER BY id, t
;

UPDATE test01.x2
SET var1 = UNHEX(var1)
;

如果我想将结果保存为表x2(删除注释标记--)，我会得到十六进制而不是字符:

+----+---+------+-----------+------------------+
| id | t | var0 | HEX(var1) | @prev_id   := id |
+----+---+------+-----------+------------------+
|  1 | 1 | a    | 0x61      |                1 |
|  1 | 3 | \N   | 0x61      |                1 |
|  1 | 7 | \N   | 0x61      |                1 |
|  1 | 8 | b    | 0x62      |                1 |
|  1 | 9 | \N   | 0x62      |                1 |
|  2 | 2 | \N   | \N        |                2 |
|  2 | 4 | u    | 0x75      |                2 |
|  2 | 5 | u    | 0x75      |                2 |
|  2 | 6 | \N   | 0x75      |                2 |
|  2 | 7 | \N   | 0x75      |                2 |
|  2 | 8 | v    | 0x76      |                2 |
|  2 | 9 | \N   | 0x76      |                2 |
+----+---+------+-----------+------------------+

有没有办法获取字符而不是十六进制？为什么会发生这种情况？
感谢您的帮助。

Answer 1

我自己通过转换 var1 找到了解决方案:

CREATE TABLE test01.x2
SELECT id, t
       , var0
       , @prev_var0 := CAST(IF(id = @prev_id AND var0 IS NULL AND @prev_var0 IS NOT NULL 
                           , @prev_var0
                               , var0 
                               ) AS CHAR
                            )  var1
       , @prev_id   := id
FROM test01.x1, (SELECT @prev_id    := NULL
                    ,@prev_var0 := NULL
        ) init
ORDER BY id, t
;

var1 的数据类型是 LONGTEXT。因为我想要 CHAR(1) 我必须修改它:

ALTER TABLE test01.x2 MODIFY var1 CHAR(1) DEFAULT NULL;

有人知道为什么显示十六进制吗？