Mysql – Why does phpMyAdmin execute all queries with an apostrophe

MySQLphpmyadmin

For example, if I use the GUI to perform an INSERT, the console will log the following execution:

enter image description here

Why does each item have apostrophes (aside from NULL)?

I know that I can do a literal INSERT via terminal or PHP without the apostrophes, especially when referencing the table or columns (and several tutorials only apply apostrophes around the insert values, not table or column references). I know that this isn't exactly a, "help me question" but I am very curious if there's a reason behind this. Thanks.

Best Answer

Those around values are single quotes - ' and denote string values. Numbers should not be quoted if they are supposed to go to a numeric type column but in PhpMyAdmin every input field is textual by default so it probably just tries to stay safe with quoting the '5' too, MySQL can convert the type when needed.

The other type are backticks ` and it is a different character. MySQL uses it to quote identifiers (names of column, tables, indexes etc.). It is not needed when you use normal identifiers like name or email but it is needed for others:

  • if you need to use a reserved word as an identifier - `insert` or `order` - not a good idea to create tables with such columns, but sometimes it already exists and you just need to query it.
  • when you want to use complex names - `column named X` - again better not to name your columns like this, but it is often used for column aliases in selects

.

select col1 as `column 1`, col2 as `column with diacritics áéí` from table;

For PMA it is just simple to quote every identifier than to check if it is safe or not to go unquoted.

Related Question