Some problems that may be caused by inconsistent MySQL encoding

Stored procedures and coding

In MySQL stored procedures, if the encoding of the table and data is different from the default encoding of the database and stored procedures, SQL may not use the index, because MySQL will perform corresponding encoding conversion on the data in the condition column. For example, in the following, the table data is latin1, and the MySQL parser will do some conversion:

... WHERE namecolumn = NAME_CONST('in_namecolumn',_utf8'MP201022' COLLATE 'utf8_general_ci')

You can do the corresponding encoding conversion in the stored procedure (usually modify the varchar/char field) so that the index can be used normally. For more information, see: mysql-slow-when-run-as-stored-proc

... WHERE namecolumn = convert(in_namecolumn using latin1) collate latin1_swedish_ci

JDBC direct connection to execute SQL

When executing SQL through jdbc connection, if the encoding is inconsistent, the varchar and char types also need to be converted, as shown below:

... WHERE namecolumn = convert(in_namecolumn using latin1) collate latin1_swedish_ci

Otherwise, the following encoding inconsistency errors may occur (depending on the mysql-connector version, the behavior may vary):

SQL state [HY000]: error code [1267]: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='

jdbc useSSL parameter changes

In the mysql-connector-java configuration, the useSSL parameter has the following differences. Starting from 5.1.38, useSSL is enabled by default for MySQL 5.5.45+, 5.6.26+ or 5.7.6+. The corresponding requireSSL and verifyServerCertificate parameters will also be enabled:

< 5.1.38:
 ConnectionProperties.useSSL=Use SSL when communicating with the server (true/false), defaults to 'false'

>= 5.1.38
 ConnectionProperties.useSSL=Use SSL when communicating with the server (true/false), default is 'true' when connecting to MySQL 5.5.45+, 5.6.26+ or 5.7.6+, otherwise default is 'false'

MySQL 5.7.x and above will enable SSL by default, and the client will automatically negotiate encryption when connecting, unless explicitly specified not to encrypt. mysql-connector-java has enabled useSSL by default since 5.1.38. So there will be no encryption problems when connecting to MySQL 5.7.x with a lower version of jdbc, but there will be encryption problems when connecting to 5.7.6+ with a higher version of jdbc. You need to explicitly specify useSSL=false, and there will be no encryption problems when connecting to MySQL 5.5, 5.6 with a higher version of jdbc.

This is the end of this article about some problems that may be caused by inconsistent MySQL encoding. For more information about problems caused by inconsistent MySQL encoding, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!