MYSQL string forced conversion method example

Preface

Since the types of the same fields in the two tables are different, or even the encoding types are different, the query will not use the index and the speed will be very slow.

Forced transfer

Let me give you a direct example:

A table id is int type B table id is char type

If you want to convert to int, it is signed

If you want to convert to char, then char

Note: The type to be converted must be the field of the table after the left join, otherwise the index will not be used.

Because the field types of the joint table are inconsistent, the index is not used.

select t.* from A tleft join B t1 on t.id = t1.id

The first conversion type

select t.* from A tleft join B t1 on t.id = cast(t1.id as signed)

The second conversion type

select t.* from A tleft join B t1 on t.id = convert(t1.id, signed)

ab=fg

However, the two fields b and g have different sorting rules. b is utf8_general_ci, and g is utf8_unicode_ci. If they are equal, SQL will report an error. So I asked my superior for help. The way to write it is:

CONVERT (ab USING utf8) COLLATE utf8_unicode_ci = fg

If the table is not indexed, the fields are transformed in the query, for example:

select a.* from a
LEFT JOIN (SELECT b.*, CONVERT (ab USING utf8) COLLATE utf8_unicode_ci = fg FROM b) a ON ab = fg

CONVERT(user_id USING utf8) COLLATE utf8_general_ci as user_id

This is the end of this article about MYSQL string conversion. For more information about MYSQL string conversion, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!