| Bug #71181 | Encoding big5 characters when insert to db via Mysql for excel | ||
|---|---|---|---|
| Submitted: | 19 Dec 2013 7:41 | Modified: | 18 Jan 2014 0:44 |
| Reporter: | Fred Li | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL for Windows: MySQL for Excel | Severity: | S3 (Non-critical) |
| Version: | OS: | Microsoft Windows | |
| Assigned to: | CPU Architecture: | Any | |
[19 Dec 2013 7:41]
Fred Li
[19 Dec 2013 7:44]
Fred Li
none
[19 Dec 2013 11:30]
MySQL Verification Team
Please provide an SQL script to recreate your database along with some of the data (including the exact character set used with it??. Thanks.
[20 Dec 2013 0:04]
Fred Li
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
CREATE TABLE IF NOT EXISTS `c_test` (
`Dates` date NOT NULL DEFAULT '0000-00-00' COMMENT '訂購日期',
`pf` varchar(10) NOT NULL COMMENT '訂購平台',
`orderer` varchar(20) NOT NULL COMMENT '訂購者',
`Cids` int(5) NOT NULL AUTO_INCREMENT COMMENT '自動編號',
PRIMARY KEY (`Cids`),
UNIQUE KEY `Cids` (`Cids`) USING BTREE,
UNIQUE KEY `check` (`orderer`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=big5 COMMENT='客戶紀錄' AUTO_INCREMENT=3 ;
INSERT INTO `c_test` (`Dates`, `pf`, `orderer`, `Cids`) VALUES
('2013-12-18', '上品', '李先生', 1),
('2013-12-18', '綠界', '林小姐', 2);
[23 Dec 2013 15:55]
Liang Guo
I'm not a mysql developer, I'm just a simplified Chinese user, I encounter a similar problem: cannot insert Chinese to mysql table with MySQL for excel. After some digg. I fount my MySQL server use latin1 Character Set. After recreate mysql server with utf8 Character Set, everything works for me. Would you like run 'show variables like '%char%';' to show your current character set? this is my output: mysql> show variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+
[23 Dec 2013 15:58]
Liang Guo
I have tested your script on my database. it works OK.
[24 Dec 2013 0:39]
Fred Li
this is my output Variable_name Value character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server utf8 character_set_system utf8
[24 Dec 2013 1:51]
Liang Guo
Which version are you using? I'm using 5.7.3.0-m13.
[24 Dec 2013 2:16]
Fred Li
Server using version is 5.1.49 mysql for excel is not supported by older versions?
[24 Dec 2013 7:51]
Liang Guo
I'm using MySQL 5.7 for Windows, and MySQL 5.5 for Linux. Both version work. Would you like try higher version? Thanks,
[18 Jan 2014 0:44]
Javier Treviño
This is a duplicate of bug 70259 (http://bugs.mysql.com/bug.php?id=70259) Tested inserting Chinese characters using MySQL for Excel, the data was inserted correctly in Chinese (verified it reading it in MySQL Workbench) and read back to Excel through an Insert Data operation correctly. This does not seem to be a valid bug. The user can reopen the bug if he can provide a configuration to reproduce the problem consistently. The behavior the user reports may be caused by a misuse of misconfiguration, by misuse I mean attempting to append data with Chinese characters into a table configured with a character set (collation) that will not accept Chinese characters (e.g. latin1); by misconfiguration I mean attempting to insert Chinese characters into a table that has a correct character set (collation) like big5 or utf8, but the server's character set variables are set to use a character set non suitable for Chinese characters.
