error 1270 mysql主从

mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: **** Master_User: ucloudbackup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 43347 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 1810 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1270 Last_Error: Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat'' on query. Default database: 'test'. Query: 'INSERT INTO test (addressType, addressName) VALUES ('sh_jincheng', concat('上海市', NAME_CONST('i',180),'室'))' Skip_Counter: 0 Exec_Master_Log_Pos: 1647 Relay_Log_Space: 43710 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1270 Last_SQL_Error: Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat'' on query. Default database: 'test'. Query: 'INSERT INTO test (addressType, addressName) VALUES ('sh_jincheng', concat('上海市', NAME_CONST('i',180),'室'))' Replicate_Ignore_Server_Ids: Master_Server_Id: 169052043 Master_UUID: a2b11ba1-1552-11e7-a33b-52540059bf44 Master_Info_File: /opt/udb/instance/mysql-5.6/2f2a1aea-b636-48b7-9006-a1af025d90d0/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 170330 22:19:10 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: a2b11ba1-1552-11e7-a33b-52540059bf44:1-105 Executed_Gtid_Set: a2b11ba1-1552-11e7-a33b-52540059bf44:1-5 Auto_Position: 0 1 row in set (0.00 sec) 

ERROR 1267 concat name_const函数 今天朋友他们有一个错误，最后是因为： MySQL> select concat('￥',NAME_CONST('tttt',1234)); ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat' 这个类似的错误原来也见过：那是因为两表关联时，关联的字段是有索引的，但是索引不是按的相同的COLLATION，所以出现的问题，修改为一样之后，即可正常。 又试了一下 MySQL> select concat('a',NAME_CONST('tttt',1234)); +-------------------------------------+ | concat('a',NAME_CONST('tttt',1234)) | +-------------------------------------+ | a1234 | +-------------------------------------+ 1 row in set (0.00 sec) MySQL> select concat('工',NAME_CONST('tttt',1234)); ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat' 从这里看，用汉字的类的就不正常。 MySQL> select COLLATION('工'); +------------------+ | COLLATION('工') | +------------------+ | utf8_general_ci | +------------------+ 1 row in set (0.00 sec) MySQL> select COLLATION('a'); +-----------------+ | COLLATION('a') | +-----------------+ | utf8_general_ci | +-----------------+ 1 row in set (0.00 sec) MySQL> select COLLATION('￥'); +------------------+ | COLLATION('￥') | +------------------+ | utf8_general_ci | +------------------+ 1 row in set (0.00 sec) MySQL> select COLLATION(NAME_CONST('tttt',1234)); +------------------------------------+ | COLLATION(NAME_CONST('tttt',1234)) | +------------------------------------+ | binary | +------------------------------------+ 1 row in set (0.00 sec) 有点晕了，前前看 'a','工','￥' 都是一样的， 汉字的不行。 看看这个函数： This function is for internal use only，可以写成别的方式 比如这里的 MySQL> select NAME_CONST('tttt',1234); +------+ | tttt | +------+ | 1234 | +------+ 1 row in set (0.00 sec) 可以写为： MySQL> select 1234 as 'tttt'; +------+ | tttt | +------+ | 1234 | +------+ 1 row in set (0.00 sec) 一样的效果 。 另： MySQL> select concat('￥',NAME_CONST('tttt','1234')); +-----------------------------------------+ | concat('￥',NAME_CONST('tttt','1234')) | +-----------------------------------------+ | ￥1234 | +-----------------------------------------+ 这样也不会报错： 不知道开发到底是什么 意思 ，和开发商量 才好最后约定怎么搞。 

delimiter // CREATE PROCEDURE `test_insert`() BEGIN DECLARE i INT DEFAULT 180;# can not be 0 WHILE i<400 DO INSERT INTO test (addressType, addressName) VALUES ('sh_jincheng', concat('上海市',i,'室')); SET i=i+1; END WHILE ; commit; END // delimiter ;