Shulou(Shulou.com)06/02 Report--

This article mainly shows you "how to solve MySql integer index and string index failure or implicit conversion problem", the content is easy to understand, well-organized, hope to help you solve your doubts, the following let Xiaobian lead you to study and learn "how to solve MySql integer index and string index failure or implicit conversion problem" this article.

Problem recurrence

First of all, let's create a user table test_user, where USER_ID is set to varchar type and unique index for effect.

CREATE TABLE test_user (ID int (11) NOT NULL AUTO_INCREMENT, USER_ID varchar (11) DEFAULT NULL COMMENT 'user account', USER_NAME varchar (25) DEFAULT NULL COMMENT 'user name', AGE int (5) DEFAULT NULL COMMENT 'age', COMMENT varchar (25) DEFAULT NULL COMMENT 'profile', PRIMARY KEY (ID) UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8

The table data is as follows (hee hee data still uses the same data as the last Mysql article MySQL uses UNION to join two queries that fail to sort the same data, but note that the table structure is different. )

IDUSER_IDUSER_NAMEAGECOMMENT1111 happy rookie 18 is very happy today 2222 sad rookie 21 is very sad 3333 serious rookie 30 is serious today 4444 happy rookie 18 is very happy 5555 serious rookie 21 is very serious today

Next, let's execute the following sql

EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111,

It is found that the explanations given are as follows:

Idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEtest_user

ALL

five

Using where

We will explain the following after putting quotation marks on the condition:

EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111'

At this point, we find that fields of type varchar use indexes when querying as strings and do not use indexes when querying with numeric types.

Extension of the problem

So the question is, if the field is integer and indexed, will it not be indexed when queried by a string? True knowledge comes from practice, so let's go on to test it.

Change the type of USER_ID to integer CREATE TABLE test_user (ID int (11) NOT NULL AUTO_INCREMENT, USER_ID int (11) DEFAULT NULL COMMENT 'user account', USER_NAME varchar (255) DEFAULT NULL COMMENT 'user name', AGE int (5) DEFAULT NULL COMMENT 'age', COMMENT varchar (ID) DEFAULT NULL COMMENT 'profile', PRIMARY KEY (ID), UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111 itself explain SELECT * FROM test_user WHERE USER_ID = '111'

After executing the above two statements, we find that fields of type int will be indexed either as a string query or as a numeric query.

The above is all the contents of the article "how to solve the problem of invalid or implicit conversion of MySql integer index and string index". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.