mysql should i use apostrophe in mysql queries?

问题:

What is the correct way of writing a query to a MySQL database on numeric data-types:

  SELECT * FROM accounts WHERE id = 5;

or

  SELECT * FROM accounts WHERE id = '5';

Mainly I prefer the last one, using ' because it is more consistent with text data-types.

Does it effect the performance?

回答1:

Quotes are for strings, MySQL is going to read those quotes and then cast it to an integer, this is slower then just handing it an int to begin with.

Honestly the performance difference is minor, but it is just like writing a program that stores numbers in strings and then casts to int when it needs to do some math. This is bad practice.

回答2:

I doubt that you can measure any noticable difference between the speed of the two queries. If you care about the performance you should ensure that you have an index on the id column. If you do, both queries will be very fast.

However there are security considerations.

The official MySQL opinion

The MySQL client security guidelines recommend that you do use quotes.

A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotation marks around the numeric constants: SELECT * FROM table WHERE ID='234'.

Emphasis mine.

My opinion

Although the documentation recommends the use of quotes, it is neither necessary nor sufficient to prevent the attack it describes. For example, changing the attacker's string to 234' OR '1'='1 would defeat their approach.

In my opinion, a better way to make your application secure is to use parameterized queries instead of putting user values directly into the string.

If for some reason you can't use parameterized queries, then don't use quotes but ensure that the variable does in fact contain an integer by using the intval function.

回答3:

Depending on the type of the id you can use either '5' or 5. Usually id is the primary key and is of type int so you should use 5

回答4:

Use quotes, when the field type in your database is a string. Otherwise, if it is numeric, do not use quotes. If you use quotes for a numeric field type it can really slow down queries, because mysql has to match strings to numbers.

回答5:

Using a quoted number against a column whose type is defined to be numeric has a teeny performance hit as compared to using an unquoted value, as the server has to convert the string to the right type during query compilation. Other than that it has no effect, and you probably will be hard pressed to measure the hit at all. (Note that as both "0" and 0 are sent to the server as strings they have to be converted to the internal type of the field before use anyway, as such sending "0" merely forces an extra step. First the parser parses out the "0" then the optimizer notices the column type is numeric and converts it accordingly. OTOH, with 0 the parser will parse it as a numeric [storing as an long int iirc], then notice the type of the field, and convert the numeric to the appropriate field type if necessary. So the difference really is insignificant).

However, using an unquoted number against a column whose type is defined to be text is a very very bad idea, as it means that server cannot use any index on the column to resolve the query.

It is important to understand that while any given string nummifies to exactly one numeric value there are a nearly infinite set of strings which nummify to a given numeric value. Consider "0", "0E0", "0.0" etc. This explains why quoting the constraint when the field is numeric is not really bad, there is only operation to perform, and it explains why NOT quoting the constraint is bad when the field is not numeric, as it means the server has to cast each string in the table to a number before doing the comparison, thus forcing a table scan.