Description:
SHOW WARNINGS immediately after EXPLAIN EXTENDED displays optimizer result in the 'Message' field. When the WHERE clause of the original sql uses a unique key, the optimizer will replace WHERE clause with 'where 1' and replace items in SELECT clause with actual values. The Message field of SHOW WARNINGS is expected to be of a certain charset ('UTF-8' in a utf8 connection), however, the actual bytes of item is always of its definition charset, for example,for a VARCHAR(10) in a table with GBK as DEFAULT CHARSET, the actual bytes is of GBK rather than UTF-8, which causes messy characters.
How to repeat:
CREATE TABLE `t_gbk` (
`id` int(11) NOT NULL,
`str` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
set names utf8;
insert t_gbk values (1, '中国');
select str from t_gbk where id = 1;
+--------+
| str |
+--------+
| 中国 |
+--------+
explain extended select str from t_gbk where id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_gbk
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select '�й�' AS `str` from `employees`.`t_gbk` where 1
1 row in set (0.00 sec)
Notice the messy characters in the message field. We can pipe to iconv to get them right.
./msb_5_6_34/use employees -e 'set names utf8; explain extended select str from t_gbk where id = 1\G show warnings\G' | iconv -f gbk -t utf8
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_gbk
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select '中国' AS `str` from `employees`.`t_gbk` where 1