Avatar of andieje
andieje
 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
MySQL ServerPHP

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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 :)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.