CODEWITHSUNDEEP
mysql
Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

MySQL 5.6 Confusing field value

Analyzing the data from my Table:

Here's my sample Query:

SELECT * from mytable where myid = 1;

Result:

enter image description here

My Question is:

why this value not equal to 1 display and consider as 1?

Anyway this value is came from GUID

and the field DataType of this field is char length of 36

Actual execution with temp data:

set @val = '1f0f69da-b508-412f-bf02-6b506f15a521';

SELECT val from
(SELECT @val as val) as a
where val = 1;

Upvotes: 1

Views: 45

Answers (1)

Alpesh Jikadra
Alpesh Jikadra

Reputation: 1722

MySQL apply implicitly conversion if necessary,

Here you are comparing char column with int value, so MySQL try to convert char column value to int while comparing the value to int like below

'1f0f69da-b508-412f-bf02-6b506f15a521' converted to 1

'2f0f69da-b508-412f-bf02-6b506f15a521' converted to 2

'102f0f69da-b508-412f-bf02-6b506f15a' converted to 102

'af0f69da-b508-412f-bf02-6b506f15a521' here it fails to convert to int.

So in your example 

set @val = '1f0f69da-b508-412f-bf02-6b506f15a521';

SELECT val from
(SELECT @val as val) as a
where val = 1;

mysql convert to 1 and because of that your query returns the result.

MySQL try to convert string to int until a non digit found.

Reference

Upvotes: 1

Related Questions