Disadvantages of quoting integers in a Mysql query

Question

I am curious about the disadvantage of quoting integers in MYSQL queries

For example

SELECT col1,col2,col3 FROM table WHERE col1='3';

VS

SELECT col1,col2,col3 FROM table WHERE col1= 3; 

If there is a performance cost, what is the size of it and why does it occur? Are there any other disavantages other that performance?

Thanks Andrew

Edit: The reason for this question
1. Because I want to learn the difference because I am curious
2. I am experimenting with a way of passing composite keys from my database around in my php code as psudo-Id-keys(PIK). These PIK's are the used to target the record. For example, given a primary key (AreaCode,Category,RecordDtm)

My PIK in the url would look like this:

index.php?action=hello&Id=20001,trvl,2010:10:10 17:10:45

And I would select this record like this:

$Id = $_POST['Id'];//equals 20001,trvl,2010:10:10 17:10:45
$sql = "SELECT AreaCode,Category,RecordDtm,OtherColumns.... FROM table WHERE (AreaCode,Category,RecordDtm) = ({$Id});
$mysqli->query($sql):
......and so on.

At this point the query won't work because of the datetime(which must be quoted) and it is open to sql injection because I haven't escaped those values. Given the fact that I won't always know how my PIK's are constructed I would write a function splits the Id PIK at the commas, cleans each part with real_escape_string and puts It back together with the values quoted. For Example: $Id = "'20001','trvl','2010:10:10 17:10:45'" Of course, in this function that is breaking apart and cleaning the Id I could check if the value is a number or not. If it is a number, don't quote it. If it is anything but a string then quote it.

Answer 1

Strings also have a different sort order from numbers.

Compare:

SELECT 312 < 41

(yields 0, because 312 numerically comes after 41)

to:

SELECT '312' < '41'

(yields 1, because '312' lexicographically comes before '41')

Depending on the way your query is built using quotes might give wrong results or none at all.

Numbers should be used as such, so never use quotes unless you have a special reason to do so.

Answer 2

Have a look at this. They did some performance evaluations between different field types.

Answer 3

The performance cost is that whenever mysql needs to do a type conversion from whatever you give it to datatype of the column. So with your query

SELECT col1,col2,col3 FROM table WHERE col1='3';

If col1 is not a string type, MySQL needs to convert '3' to that type. This type of query isn't really a big deal, as the performance overhead of that conversion is negligible.

However, when you try to do the same thing when, say, joining 2 table that have several million rows each. If the columns in the ON clause are not the same datatype, then MySQL will have to convert several million rows every single time you run your query, and that is where the performance overhead comes in.

Answer 4

It gives the wrong impression about the data type for the column. As an outsider, I assume the column in question is CHAR/VARCHAR & choose operations accordingly.

Otherwise MySQL, like most other databases, will implicitly convert the value to whatever the column data type is. There's no performance issue with this that I'm aware of but there's a risk that supplying a value that requires explicit conversion (using CAST or CONVERT) will trigger an error.

Answer 5

According to me, I think there is no performance/size cost in the case you have mentioned. Even if there is, then it is very much negligible and wont affect your application as such.