CODEWITHSUNDEEP
mysqlsql
Sumeet
Sumeet

Reputation: 1799

mysql returns all rows with join with slash in value

Why this query is returing all rows with / while using equal to in join

MySQL 5.6 Schema Setup:

CREATE TABLE entity
(
id int(10) unsigned not null AUTO_INCREMENT,
parent_id int(10) unsigned,
title varchar(200),
path varchar(200),
primary key(id),
CONSTRAINT `SelfKey` FOREIGN KEY (`parent_id`) REFERENCES `entity` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);
INSERT INTO entity (id, parent_id, title,path)
VALUES (1, null, 'parent title','1'),(2, 1, 'child 1','1/2'),
(3, 1, 'child 2','1/3')

Query 1:

select c.title,c.path from entity e inner join entity c on c.path = e.id

Results:

|   title        | path |
|----------------|------|
| parent title   |    1 |
| child 1        |  1/2 |
| child 2        |  1/3 |

I am thinking of using 

select c.title,c.path from entity e inner join entity c on c.path = e.id

instead of

select c.title,c.path from entity e inner join entity c on c.path like concat(e.id,'/%')

the first one gives much better performence and also returns parent row along with children then the second one

Upvotes: 2

Views: 181

Answers (1)

ratsbane
ratsbane

Reputation: 890

Path is defined as a varchar and id is an integer. MySQL will implicitly cast the varchar to an unsigned integer. When it's converting 1/3 to an integer it will start at the left and read characters until it finds one that's not a digit (in this case /) and everything before that will be converted to an int. Thus, 1/3 converts to 1:

select cast('1/3' as unsigned)

+ ---------------------------- +
| cast('1/3' as unsigned)      |
+ ---------------------------- +
| 1                            |
+ ---------------------------- +

See MySQL Type Conversion

Upvotes: 1

Related Questions