MySQL SELECT和CREATE给出了不同的结果：字符分别是十六进制 [英] MySQL SELECT and CREATE gives different results: Character respectively hexadecimal
问题描述
我在不同的时间点t有一个人（id）和一个特征（var0）。在某些时间点，特征缺失，我想用前一个值填补空缺。下面是一个表格的例子：
I have a table with person (id) and one characteristic (var0) at different timepoints t. At some timepoints the characteristic is missing and I would like to fill the gaps with the former value. Here is an example of a table:
+---+---+----+ +----+---+------+------+------------------+
|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 |
+---+---+----+ +----+---+------+------+------------------+
左边的表是orignal x1表，右表是请求的结果。下面是得到结果的代码：
The left table is the orignal x1 table and the right table is the requested results. Here is the code to get the result:
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（删除注释标记 - ），我会得到十六进制而不是字符：
If I want to save the result as table x2 (remove comments marks --), I get hexadecimals instead of characters:
+----+---+------+-----------+------------------+
| 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 |
+----+---+------+-----------+------------------+
有没有办法让字符而不是十六进制？为什么会发生这种情况？
感谢您的帮助。
Is there a way to get characters instead of the hexadecimal? Why does this happened?
Thanks for helps.
推荐答案
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）我必须修改它：
The datatype of var1 is LONGTEXT. Since I want to have CHAR(1) I have to modfiy it:
ALTER TABLE test01.x2 MODIFY var1 CHAR(1) DEFAULT NULL;
任何人都知道为什么要显示十六进制数字？
Anyone has an idea why the hexadecimal is displayed?
这篇关于MySQL SELECT和CREATE给出了不同的结果：字符分别是十六进制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
