Quotes
A common error in SQL is with quotes. Luckily, compared to other databases, MariaDB and MySQL are very forgiving.
For example, suppose I have a “products” table for an online store. If I want to look up every product which has a price of 5.99 or more I might use the following query:
MariaDB [crashcourse]> select prod_id, prod_name, prod_price from products -> where prod_price >= 5.99; +---------+----------------+------------+ | prod_id | prod_name | prod_price | +---------+----------------+------------+ | ANV01 | .5 ton anvil | 5.99 | | ANV02 | 1 ton anvil | 9.99 | | ANV03 | 2 ton anvil | 14.99 | | OL1 | Oil can | 8.99 | | TNT2 | TNT (5 sticks) | 10.00 | | FB | Bird seed | 10.00 | | SAFE | Safe | 50.00 | | DTNTR | Detonator | 13.00 | | JP1000 | JetPack 1000 | 35.00 | | JP2000 | JetPack 2000 | 55.00 | +---------+----------------+------------+ 10 rows in set (0.02 sec)
MariaDB and MySQL are also perfectly happy if the “where” line is changed to
where prod_price >= '5.99';
or even (if sql_mode is not “ANSI_QUOTES”):
where prod_price >= "5.99";
All of the above work because the value being looked up is an integer. If instead I want to see all of the anvils I’m selling I might use a query such as:
MariaDB [crashcourse]> select prod_id, prod_name, prod_price from products -> where prod_name like '%anvil%'; +---------+--------------+------------+ | prod_id | prod_name | prod_price | +---------+--------------+------------+ | ANV01 | .5 ton anvil | 5.99 | | ANV02 | 1 ton anvil | 9.99 | | ANV03 | 2 ton anvil | 14.99 | +---------+--------------+------------+ 3 rows in set (0.02 sec)
However, if I execute the query without any quotes or if I use double quotes (“) around the search string when sql_mode = “ANSI_QUOTES” I will see an error:
MariaDB [crashcourse]> select prod_id, prod_name, prod_price from products -> where prod_name like %anvil%; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%anvil%' at line 2
Basically, without the quotes, or when sql_mode = “ANSI_QUOTES”, the server does not know how to interpret the ‘%anvil%’ string.
To be safe, the rules I always try to follow are:
- use single quotes (‘) around strings
- do not use quotes around integers
- avoid the use of double quotes (“)
BTW: The examples above use the sample data from MariaDB Crash Course by Ben Forta.