Foutbericht op MySql:
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
Ik heb verschillende andere berichten gelezen en heb dit probleem niet kunnen oplossen.
Het betrokken deel is ongeveer als volgt:
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;
De opgeslagen procedure die ik gebruik is deze:
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
Ik was aan het testen met php, maar dezelfde fout wordt gegeven met SQLyog.
Ik heb ook getest om de hele database opnieuw te maken, maar zonder resultaat.
Alle hulp wordt zeer op prijs gesteld.
Antwoord 1, autoriteit 100%
De standaardsortering voor parameters van opgeslagen procedures is utf8_general_cien u kunt sorteringen niet combineren, dus u heeft vier opties:
Optie 1: voeg COLLATEtoe aan uw invoervariabele:
SET @rUsername = ‘aname’ COLLATE utf8_unicode_ci; -- COLLATE added
CALL updateProductUsers(@rUsername, @rProductID, @rPerm);
Optie 2: voeg COLLATEtoe aan de WHERE-clausule:
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
Optie 3: voeg het toe aan de parameterdefinitie IN(pre-MySQL 5.7):
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
Optie 4: wijzig het veld zelf:
ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_general_ci;
Tenzij u gegevens in Unicode-volgorde moet sorteren, raad ik u aan al uw tabellen te wijzigen om utf8_general_ci-sortering te gebruiken, omdat er geen codewijzigingen nodig zijn en het sorteren iets sneller gaat.< /strike>
UPDATE: utf8mb4/utf8mb4_unicode_ci is nu de geprefereerde tekenset/sorteermethode. utf8_general_ci wordt afgeraden, omdat de prestatieverbetering verwaarloosbaar is. Zie https://stackoverflow.com/a/766996/1432614
Antwoord 2, autoriteit 13%
Ik heb een halve dag besteed aan het zoeken naar antwoorden op een identieke “Illegale mix van sorteringen”-fout met conflicten tussen utf8_unicode_ci en utf8_general_ci.
Ik ontdekte dat sommige kolommen in mijn database niet specifiek utf8_unicode_ciwaren verzameld. Het lijkt erop dat mysql deze kolommen impliciet heeft verzameld utf8_general_ci.
In het bijzonder leverde het uitvoeren van een ‘SHOW CREATE TABLE table1’-query iets als het volgende op:
| table1 | CREATE TABLE `table1` (
`id` int(11) NOT NULL,
`col1` varchar(4) CHARACTER SET utf8 NOT NULL,
`col2` int(11) NOT NULL,
PRIMARY KEY (`col1`,`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
Let op de regel ‘col1’ varchar(4) CHARACTER SET utf8 NOT NULLheeft geen sortering gespecificeerd. Ik heb toen de volgende query uitgevoerd:
ALTER TABLE table1 CHANGE col1 col1 VARCHAR(4) CHARACTER SET utf8
COLLATE utf8_unicode_ci NOT NULL;
Hiermee is mijn fout ‘Illegale mix van sorteringen’ opgelost. Ik hoop dat dit iemand anders kan helpen.
Antwoord 3, autoriteit 3%
Ik had een soortgelijk probleem, maar het kwam bij me op binnen de procedure, toen mijn queryparameter werd ingesteld met behulp van een variabele, b.v. SET @value='foo'.
De oorzaak hiervan was een niet-overeenkomende collation_connectionen Database-sortering. collation_connectiongewijzigd om overeen te komen met collation_databaseen het probleem is verdwenen. Ik denk dat dit een elegantere benadering is dan het toevoegen van COLLATE na parameter/waarde.
Samenvattend: alle sorteringen moeten overeenkomen. Gebruik SHOW VARIABLESen zorg ervoor dat collation_connectionen collation_databaseovereenkomen (controleer ook de tabelsortering met SHOW TABLE STATUS [table_name]).
Antwoord 4, autoriteit 2%
Een beetje vergelijkbaar met @bpile-antwoord, mijn geval was een my.cnf-invoerinstelling collation-server = utf8_general_ci. Nadat ik me dat realiseerde (en nadat ik alles hierboven had geprobeerd), schakelde ik mijn database krachtig over naar utf8_general_ci in plaats van utf8_unicode_ci en dat was het dan:
ALTER DATABASE `db` CHARACTER SET utf8 COLLATE utf8_general_ci;
Antwoord 5
Het antwoord is toevoegen aan het antwoord van @Sebas – het instellen van de sortering van mijn lokale omgeving. Probeer dit niet op productie.
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Bron van deze oplossing
Antwoord 6
In mijn eigen geval heb ik de volgende foutmelding
Illegale mix van sorteringen (utf8_general_ci,IMPLICIT) en (utf8_unicode_ci,IMPLICIT) voor bewerking ‘=’
$this->db->select(“users.username as matric_no, CONCAT(users.achternaam,
‘ ‘,
gebruikers.voornaam, ”, gebruikers.achternaam) als volledige naam”)
->join(‘users’, ‘users.username=classroom_students.matric_no’, ‘left’)
->where(‘classroom_students.session_id’, $session)
->where(‘classroom_students.level_id’, $level)
->where(‘classroom_students.dept_id’, $dept);
Na weken van het zoeken van Google merkte ik dat de twee velden die ik vergelijken, uit verschillende collatiegaam bestaan. De eerste I.E-gebruikersnaam is van UTF8_GEERAL_CI, terwijl de tweede is van UTF8_UNICODE_CI, dus ik ging terug naar de structuur van de tweede tabel en veranderde het tweede veld (MATRIC_NO) tot UTF8_GENERAL_CI en het werkte als een charme.
Antwoord 7
Ondanks het vinden van een enorm aantal vraag over hetzelfde probleem (1 , 2 , 3 , 4 ) Ik heb nog nooit een antwoord gevonden dat in overweging nam, zelfs hier.
Hoewel er al meerdere werkoplossingen zijn gegeven, zou ik een prestatie-overweging willen doen.
EDIT: Dank aan Manatax om erop te wijzen dat optie 1 niet lijdt aan prestatieproblemen.
Gebruik Optie 1 en 2 , AKA De Collecte Cast-aanpak, kan leiden tot potentiële knelpunten, omdat een index is gedefinieerd in de kolom Niet worden gebruikt, waardoor een volledige scan .
Hoewel ik optie 3 niet uitprobeert, is mijn Hunch dat het dezelfde gevolgen zal hebben van optie 1 en 2.
Ten slotte, Optie 4 is de beste optie voor zeer grote tabellen wanneer het haalbaar is. Ik bedoel, er zijn geen ander gebruik dat afhankelijk is van de oorspronkelijke collatie.
Overweeg deze vereenvoudigde query:
SELECT
*
FROM
schema1.table1 AS T1
LEFT JOIN
schema2.table2 AS T2 ON T2.CUI = T1.CUI
WHERE
T1.cui IN ('C0271662' , 'C2919021')
;
In mijn originele voorbeeld had ik nog veel meer joins.
Natuurlijk hebben Table1 en Table2 verschillende collations.
Met behulp van de Sorteer Operator om te casten, het zal leiden tot indexen die niet worden gebruikt.
Zie SQL-uitleg in de onderstaande afbeelding.
visuele query-uitleg bij gebruik van de collectie cast
Aan de andere kant, Optie 4 kan voordelen van mogelijke index en geleid tot snelle query’s.
In de onderstaande afbeelding ziet u dezelfde query die wordt uitgevoerd na het toepassen Optie 4 , AKA wijzigen van de schema / tabel / kolomvervaging.
visuele query-uitleg nadat de collatie is gewijzigd, en daarom zonder de collectie
Concluderend, als de prestaties belangrijk zijn en u de collatie van de tabel kunt wijzigen, gaat u voor Optie 4 .
Als u op een enkele kolom moet handelen, kunt u iets dergelijks gebruiken:
ALTER TABLE schema1.table1 MODIFY `field` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Antwoord 8
Dit gebeurt waar een kolom expliciet is ingesteld op een andere collatie of de standaardopnames verschilt in de opgevraagde tabel.
Als u veel tabellen hebt, wilt u deze query wijzigen:
select concat('ALTER TABLE ', t.table_name , ' CONVERT TO CHARACTER
SET utf8 COLLATE utf8_unicode_ci;') from (SELECT table_name FROM
information_schema.tables where table_schema='SCHRMA') t;
Hiermee wordt de query’s uitgevoerd die nodig zijn om alle tabellen om te zetten om de juiste collatie per kolom
te gebruiken