current position:Home>1267 - Illegal mix of collations (MySQL connection table query error)

1267 - Illegal mix of collations (MySQL connection table query error)

2022-11-24 22:51:28bald love fitness

report error

Insert image description here

Cause Analysis

Judging from the error report, the Collation code of the prompt table is inconsistent;

Use the following SQL to view the encoding of the two tables separately;

SHOW TABLE STATUS FROM `oms-order` LIKE 'oms_sold_out_exception_line'

Where oms-order is the database name, oms_sold_out_exception_line is the table name;

Insert image description here
insert image description here

From the results, the Collation of the two tables is indeed inconsistent and needs to be adjusted.

PS: There is a table that the author copied from other databases, in order to combine and query some data.

Solution

Method 1: Modify the table structure

Adjust the table structure of the two tables to be the same;

ALTER TABLE `item_center__item` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

Method 2: Use the CONVERT function (recommended)

Maybe due to some historical reasons, the encoding of the table should be different!!At this time we can only consider using the CONVERT function.

select a.* from oms_sold_out_exception_line a, item_center__item b where CONVERT ( a.sku_id USING utf8 ) = CONVERT ( b.code USING utf8 )

Overview

When initially setting up the table structure, try to make the table codes of all libraries the same to avoid unnecessary pitfalls.

copyright notice
author[bald love fitness],Please bring the original link to reprint, thank you.
https://en.chowdera.com/2022/328/202211242243375834.html

Random recommended