[MySQL]Error Code: 1366. Incorrect string value

        同事遇到上面這個錯誤，這是字元集設錯的問題，或是說定序設錯的關係，反正就是編碼的問題啦，MySQL與MS SQL一樣是可以從Server到Database到Table的Column都可設定，以下示範一下Column層級的差異囉，以免下次自己又忘了

drop table if EXISTS t1_utf8;

drop table if EXISTS t1_latin;

drop table if EXISTS t1_utf8_2;

drop table if EXISTS t1_latin_2;

CREATE TABLE t1_utf8 (

    `id` int(11) NOT NULL,

    `text1` varchar(32) COLLATE utf8_unicode_ci NOT NULL,

    `text2` varchar(32) COLLATE big5_chinese_ci NOT NULL

)  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE = utf8_unicode_ci;

CREATE TABLE t1_latin (

    `id` int(11) NOT NULL,

    `text` varchar(32) COLLATE latin1_general_ci NOT NULL,

    `text2` varchar(32) COLLATE big5_chinese_ci NOT NULL

)  ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE = latin1_general_ci;

CREATE TABLE t1_utf8_2 (

    `id` int(11) NOT NULL,

    `text` varchar(32) COLLATE latin1_general_ci NOT NULL,

    `text2` varchar(32) COLLATE big5_chinese_ci NOT NULL

)  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE = utf8_unicode_ci;

CREATE TABLE t1_latin_2 (

    `id` int(11) NOT NULL,

    `text` varchar(32) COLLATE utf8_unicode_ci NOT NULL,

    `text2` varchar(32) COLLATE big5_chinese_ci NOT NULL

)  ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE = latin1_general_ci;

select

    TABLE_SCHEMA,

    TABLE_NAME,

    COLUMN_NAME,

    COLLATION_NAME,

    ORDINAL_POSITION,

    COLUMN_DEFAULT,

    DATA_TYPE,

    CHARACTER_SET_NAME,

    COLLATION_NAME

from

    information_schema.COLUMNS

where  table_name like 't1%';

SELECT

    TABLE_SCHEMA, Table_name, engine, TABLE_COLLATION

FROM

    information_schema.TABLES

where  table_name like 't1%';

insert into t1_utf8 values ('1', 'utf8測試1', 'utf8測試2');

insert into t1_latin values ('1', 'latin測試1', 'latin測試2');

insert into t1_utf8_2 values ('1', 'utf8測試1', 'utf8測試2');

insert into t1_latin_2 values ('1', 'latin測試1', 'latin測試2');

select

    *

from

    t1_utf8

union select

    *

from

    t1_latin_2;

        反正只要Column設對，基本上client對應也是對的，應該就OK囉

zh-CN → zh-TW

免下次自己又忘了