parametros - stored procedure mysql insert

Mezcla ilegal de intercalaciones(utf8_unicode_ci, IMPLICIT) y(utf8_general_ci, IMPLICIT) para la operación ''=''

Question

Error msg en MySql: mezcla ilegal de intercalaciones (utf8_unicode_ci, IMPLICIT) y (utf8_general_ci, IMPLICIT) para la operación ''=''

He revisado otras publicaciones y no pude resolver este problema. La parte afectada es algo similar a esto:

CREATE TABLE users (
userID INT UNSIGNED NOT NULL AUTO_INCREMENT,
firstName VARCHAR(24) NOT NULL,
lastName VARCHAR(24) NOT NULL,
username VARCHAR(24) NOT NULL,
password VARCHAR(40) NOT NULL,
PRIMARY KEY (userid)
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE products (
productID INT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(104) NOT NULL,
picturePath VARCHAR(104) NULL,
pictureThumb VARCHAR(104) NULL,
creationDate DATE NOT NULL,
closeDate DATE NULL,
deleteDate DATE NULL,
varPath VARCHAR(104) NULL,
isPublic TINYINT(1) UNSIGNED NOT NULL DEFAULT ''1'',
PRIMARY KEY (productID)
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE productUsers (
productID INT UNSIGNED NOT NULL,
userID INT UNSIGNED NOT NULL,
permission VARCHAR(16) NOT NULL,
PRIMARY KEY (productID,userID),
FOREIGN KEY (productID) REFERENCES products (productID) ON DELETE RESTRICT ON UPDATE NO ACTION,
FOREIGN KEY (userID) REFERENCES users (userID) ON DELETE RESTRICT ON UPDATE NO ACTION
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

El procedimiento almacenado que estoy usando es este:

CREATE PROCEDURE updateProductUsers (IN rUsername VARCHAR(24),IN rProductID INT UNSIGNED,IN rPerm VARCHAR(16))
BEGIN
UPDATE productUsers
INNER JOIN users
ON productUsers.userID = users.userID
SET productUsers.permission = rPerm
WHERE users.username = rUsername
AND productUsers.productID = rProductID;
END

Estuve probando con php, pero el mismo error se da con SQLyog. También probé recreando todo el DB, pero no sirvió para nada.

Cualquier ayuda será muy apreciada.

Answer 1

Hay cuatro opciones:

Opción 1 : agregue COLLATE a su variable de entrada:

SET @rUsername = ‘aname’ COLLATE utf8_unicode_ci; -- COLLATE added
CALL updateProductUsers(@rUsername, @rProductID, @rPerm);

Opción 2 : agregue COLLATE a la cláusula WHERE :

CREATE PROCEDURE updateProductUsers(
IN rUsername VARCHAR(24),
IN rProductID INT UNSIGNED,
IN rPerm VARCHAR(16))
BEGIN
UPDATE productUsers
INNER JOIN users
ON productUsers.userID = users.userID
SET productUsers.permission = rPerm
WHERE users.username = rUsername COLLATE utf8_unicode_ci -- COLLATE added
AND productUsers.productID = rProductID;
END

Opción 3 : agréguela a la definición del parámetro IN :

CREATE PROCEDURE updateProductUsers(
IN rUsername VARCHAR(24) COLLATE utf8_unicode_ci, -- COLLATE added
IN rProductID INT UNSIGNED,
IN rPerm VARCHAR(16))
BEGIN
UPDATE productUsers
INNER JOIN users
ON productUsers.userID = users.userID
SET productUsers.permission = rPerm
WHERE users.username = rUsername
AND productUsers.productID = rProductID;
END

Opción 4 : alterar el campo en sí:

ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_general_ci;

ya que la intercalación predeterminada para los parámetros del procedimiento almacenado es utf8_general_ci y no puede mezclar intercalaciones.

A menos que necesite ordenar datos en orden Unicode, le sugiero que altere todas sus tablas para usar la intercalación utf8_general_ci , ya que no requiere cambios de código, y la velocidad se ordena ligeramente.

Answer 2

Pasé medio día buscando respuestas a un error idéntico de "mezcla ilegal de intercalaciones" con conflictos entre utf8_unicode_ci y utf8_general_ci.

Descubrí que algunas columnas de mi base de datos no estaban compiladas específicamente utf8_unicode_ci . Parece que mysql compiló estas columnas utf8_general_ci implícitamente .

Específicamente, ejecutar una consulta ''SHOW CREATE TABLE tabla1'' arrojó algo como lo siguiente:

| table1 | CREATE TABLE `table1` (
`id` int(11) NOT NULL,
`col1` varchar(4) CHARACTER SET utf8 NOT NULL,
`col2` int(11) NOT NULL,
PRIMARY KEY (`photo_id`,`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

Tenga en cuenta que la línea ''col1'' varchar (4) CHARACTER SET utf8 NOT NULL no tiene una intercalación especificada. Luego ejecuté la siguiente consulta:

ALTER TABLE table1 CHANGE col1 col1 VARCHAR(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

Esto resolvió mi error de "combinación ilegal de intercalaciones". Espero que esto pueda ayudar a alguien más allá.

Answer 3

Tuve un problema similar, pero se me ocurrió realizar un procedimiento interno, cuando mi parámetro de consulta se configuró utilizando una variable, por ejemplo, SET @value=''foo'' .

Lo que estaba causando esto no coincidía con collation_connection y la intercalación de la base de datos. Cambió la collation_connection de collation_connection para que coincida con collation_database y el problema desapareció. Creo que este es un enfoque más elegante que agregar COLLATE después de param / value.

En resumen: todas las intercalaciones deben coincidir. Use SHOW VARIABLES y asegúrese de que collation_connection y collation_database coinciden (también consulte la clasificación de la tabla usando SHOW TABLE STATUS [table_name] ).

Answer 4

Un poco similar a la respuesta @bpile, mi caso fue una configuración de entrada my.cnf collation-server = utf8_general_ci . Después de darme cuenta (y después de probar todo lo anterior), cambié mi base de datos a utf8_general_ci en lugar de utf8_unicode_ci y eso fue todo:

ALTER DATABASE `db` CHARACTER SET utf8 COLLATE utf8_general_ci;