CODEWITHSUNDEEP
mysqlsql
Nick
Nick

Reputation: 9041

How to avoid wrong data type query in MySQL

We have a table with a column criticality_id stored as 0, 1, 2, 3, which represent undefined, A,B,C respectivly. One of my colleagues would like to show all items of criticality C, but he accidentially queries the database with a wrong query:

select * from mytable where criticality_id = 'C'

This surprisingly retrieved all the items with criticality_id 0.

After a few googling, I know that MySQL will do automatically type converting, so here 'C' = 0. The query result is technically correct but surely not what he wants. My question is how to avoid such kind of mismatch, return strictly matched result. If none matches, return nothing.

Upvotes: 1

Views: 241

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

One simple but somewhat eclectic solution would be to change the data type to CHAR(1).

Then:

criticality_id = '0'   --> true when criticality is 0
criticality_id = 0     --> true when criticality is 0
criticality_id = 'C'   --> false when criticality is 0

I don't know if there is a setting to remove implicit conversion.

Upvotes: 1

Bazgrim
Bazgrim

Reputation: 189

Unless you're working with a language that automatically sets data types. You need to document what your schema for your tables look like. It is unwise to take in random data that has an unknown type.

Nothing should ever be "mystery data" that you're working with, save few minor exceptions.

Upvotes: 0

Related Questions