We help IT Professionals succeed at work.

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

andieje
andieje asked
on
1,173 Views
Last Modified: 2014-05-16
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
Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
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

Author

Commented:
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.
Most Valuable Expert 2011
Author of the Year 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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 :)
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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

Author

Commented:
Fantastic :)
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Thanks for the points and thanks for using EE, ~Ray
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.