Mysql – Error Code: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation ‘=’ error on simple select query

MySQL

I am getting

#1267 – Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '='

this error on below query

SELECT * FROM 
sm_product_inventories_list 
WHERE  inventory_date =  CURDATE() 
AND  inventory_time =  CURTIME()

I tried to change collation of table by

ALTER TABLE sm_product_inventories_list CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

still I am getting error.
Type of inventory_date is date and inventory_time is varchar

Thanks in advance.

Best Answer

A quick solution is to add "binary" before comparing as follows,

WHERE binary inventory_date = binary CURDATE() AND binary inventory_time = binary CURTIME()

Related Solutions

Mysql – Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation ‘find_in_set’

If you are using mysqli, issue this command right after connecting:

$mysqli->set_charset("utf8");

This will set your connection encoding to UTF8 (same as your table uses).

With plain mysql, use this:

mysql_query("SET NAMES utf8", $conn);
mysql_query("SET CHARACTER SET utf8", $conn);

Mysql – Illegal mix of collations error in MySql

Here's how to check which columns are the wrong collation:

SELECT table_schema, table_name, column_name, character_set_name, collation_name

FROM information_schema.columns

WHERE collation_name = 'latin1_general_ci'

ORDER BY table_schema, table_name,ordinal_position;

And here's the query to fix it:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin1 COLLATE 'latin1_swedish_ci';

Link

Best Answer

Related Solutions

Mysql – Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation ‘find_in_set’

Mysql – Illegal mix of collations error in MySql

Related Topic