the id in MySQL table is uuid, and no id is 1.
A. My query is:
select id, name from xxx_table where id=1
and I get the result:
-------------------------------------- ----------------------
| id | name |
-------------------------------------- ----------------------
| 1bdf0336-c5bf-4245-8897-dbda3bf9e202 | xxxxxxxxxxxxxxxxxxxx |
-------------------------------------- ----------------------
that's not what I want. this id is not 1.
B. my new query:
select id, name from xxx_table where id='1'
and I get Empty set, that is what I want.
What I'm confused is, if it's the format issue of id, why int(1) can match uuid(1bdf0336-c5bf-4245-8897-dbda3bf9e202)?
================================================================ OK. Follow the suggestion of Luuk, when I check
show create table xxx_table;
and I get:
| Table | Create Table |
| xxx_table | CREATE TABLE `xxx_table` (`id` varchar(64) NOT NULL,
...,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
CodePudding user response：
MySQL plays loose with type conversion. When implicitly converting a char to a number.
When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts strings to numbers as necessary, and vice versa.
For example, there is a query like below, you will get result 2 instead of an error from the query.
because Mysql will convert the query like 1 1 implicitly.
Query 1:
select '1bdf0336-c5bf-4245-8897-dbda3bf9e202' 1 res
| res |
|-----|
| 2 |
this query will compare full string which equals to 1 string type
select id, name from xxx_table where id='1'
The following rules describe how conversion occurs for comparison operations:
1 - If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
2 - If both arguments in a comparison operation are strings, they are compared as strings.
3 - If both arguments are integers, they are compared as integers.
4 - Hexadecimal values are treated as binary strings if not compared to a number.
5 - If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
6 - A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.
7 - If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
8 - In all other cases, the arguments are compared as floating-point (real) numbers.
CodePudding user response：
After the SELECT ... you will see x warnings. Use SHOW WARNINGS to find out about those warnings:
mysql> select * from xxx_table where id=12;
--------------------------------------
| id |
--------------------------------------
| 12a1c7d5-6dfa-11ec-9124-309c23b7280c |
--------------------------------------
1 row in set, 5 warnings (0.00 sec)
mysql> show warnings;
--------- ------ --------------------------------------------------------------------------
| Level | Code | Message |
--------- ------ --------------------------------------------------------------------------
| Warning | 1292 | Truncated incorrect DOUBLE value: '12a1c7d5-6dfa-11ec-9124-309c23b7280c' |
This can also be seen when doing:
mysql> select id, cast(id as unsigned) from xxx_table;
-------------------------------------- ----------------------
| id | cast(id as unsigned) |
-------------------------------------- ----------------------
| 12a1c7d5-6dfa-11ec-9124-309c23b7280c | 12 |
| 13392fc5-6dfa-11ec-9124-309c23b7280c | 13392 |
| 13ad01fd-6dfa-11ec-9124-309c23b7280c | 13 |
| 1425df26-6dfa-11ec-9124-309c23b7280c | 1425 |
| 14a139e8-6dfa-11ec-9124-309c23b7280c | 14 |
-------------------------------------- ----------------------
5 rows in set, 5 warnings (0.00 sec)
P.S. The long story is about type conversion, see other answer.
CodePudding user response：
The other answers have dealt with the type-conversion in plenty of detail so I thought I would suggest that you look at the other issue. Why are you storing UUID in a varchar? It may be easy but it is very inefficient as you significantly increase the size of all your indices. Storing them in a BINARY(16) would make more sense.
CREATE TABLE `uuid_tests` (
`uuid` BINARY(16) NOT NULL PRIMARY KEY,
`string` varchar(64) NOT NULL
);
INSERT INTO uuid_tests VALUES
(UUID_TO_BIN('1bdf0336-c5bf-4245-8897-dbda3bf9e202'), '1bdf0336-c5bf-4245-8897-dbda3bf9e202');
SELECT * FROM uuid_tests WHERE uuid = UUID_TO_BIN('1bdf0336-c5bf-4245-8897-dbda3bf9e202');
SELECT * FROM uuid_tests WHERE uuid = 1;
SELECT * FROM uuid_tests WHERE string = '1bdf0336-c5bf-4245-8897-dbda3bf9e202';
SELECT * FROM uuid_tests WHERE string = 1;