asked on

Inserting/updating an integer field with the number in single quotes

Hello

I am using php to insert/update mysql and the code I have automatically puts all parameters in quotes for an insert/update statement etc. I have read elsewhere that this is generally considered bad practice

http://stackoverflow.com/questions/6781976/mysql-quote-numbers-or-not

One of the reasons was that if the integer that is in quotes is in a where clause then the optimiser cant use the index.
However I was wondering if an integer is in quotes in an insert or update statement, will mysql still create/update the indexes on that column properly?

Thanks a lot in advance

Ray Paseur

Great question! It is not a bad practice to quote numbers. You will never be able to find the amount of CPU time it costs, and one advantage of quoting is that it helps avoid the query that says, "WHERE id=123 OR 1=1". That will dump the data base. If it says, "WHERE id='123 OR 1=1' the injected OR clause will not be treated as a SQL directive and your data will remain safe.

The center of gravity in MySQL performance can almost always be found in these issues:

1. Have an index on every column used in WHERE, ORDER, JOIN, HAVING, GROUP
2. NEVER use SELECT * - instead name the columns in the query
3. Use a LIMIT clause in every query where you do not absolutely require a complete table scan
4. Use EXPLAIN SELECT on every query that touches more than one table

Good article here:
https://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html

andieje

ASKER

Hi Ray

Thanks for some very useful information. They can be my 4 commandments :)

However for curiosity's sake I am still wondering if the index will be created properly on a number column if the number is in quotes. As you say the 4 key performance factors are given above. I'm just curious now.

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

andieje

ASKER

Blimey thats ace. And interesting. I dont know if you can follow people posting questions but I would love it if you could follow me as Im doing a php project which is really quite advanced and guess what ive never done php or ajax before so its sink or swim for me :)

Ray Paseur

I don't think we have the "follow me" functionality yet, but there is a Product Advisory Committee and EE is listening to our requests. I only follow the PHP Zone, but I usually see everything that's in that Zone if it stays open for more than 24 hours. So if your questions are in the PHP Zone I'll see them and I'll certainly respond if I can help!

All the best, ~Ray

andieje

ASKER

Fantastic :)

Ray Paseur

Thanks for the points and thanks for using EE, ~Ray