Connection on columns of different types?

Question

If one column is of type int and has a value of 10. Another column is of type varchar and has a value of "10". Is it safe to join these values ​​(mySql), and am I getting the same result as if both were of type int and value 10?

I need to have them of different types, because in the same table the column is an auto-increment key (therefore it must be int). Another column (in another table) can sometimes contain values ​​with letters, so I have to make it varchar. Can you look at any huge problems with this or maybe I should revise my schema?

Answer 1

There are two problems with this approach:

• MySQL will not use indexes for such a join - which may or may not be a huge deal depending on your circumstances (table size / query / etc ...)

• Database design - it smells. Perhaps this is my poor imagination :-), but I can not come up with an example where something like this would be justified. If it is a primary key in one table, it must be a foreign key in another. Can you explain what you are trying to do in more detail?

Answer 2

you should be fine while you explicitly convert the data so that the comparison ends in equivalent data types.

based on your description, you will need to convert the int type to varchar before doing the comparison. take care to trim the gaps and remember if capitalization is set on your db. also - int comparisons are much faster than varchar comparisons.