-4

Trying to load the data from my SQL tables with the following statement

select `*` from `table_name`

Unfortunately, it throws an error in the MySQL 5.6.33 version and another side it works fine in the 5.7.25 version.

I'm confused where it is the configuration thing or it is due to the version change because I'm not able to find out the clear documentation on the above thing.

Also, what is the preferred way to write a select statement? Should I go with `` or not.

aMoL Thite
  • 951
  • 1
  • 7
  • 18
  • 7
    it's `select *` - not `select \`*\`` – treyBake Apr 25 '19 at 11:12
  • 2
    also pos dupe of https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql – treyBake Apr 25 '19 at 11:12
  • Hey, @treyBake I have figured it out it's working with without Backticks I want to know the reason behind this whether it is the version change or the configuration thing. and what is the preferred way and why? Also, the mentioned post generally written for the column names and values – aMoL Thite Apr 25 '19 at 11:17
  • 2
    Ticks means its either a column or a table. `*` selects *every* column, and it is not a column itself. If you for some reason had a specific column that was actually named `*`, you could wrap it in ticks and select from it that way. (Which obviously is a really bad idea to have a column called `*`). Long story short: `*` is an operator. Ticks are used around columns and tables, not operators. – Qirel Apr 25 '19 at 11:19
  • @aMoLThite nope, this has been the case since release. Qirel gives a good explanation as to why (reason for linked dupe is to show differences between the quotes.. as you spotted, it's for column names, * is not a column) – treyBake Apr 25 '19 at 11:20
  • @Qirel Completely agree with you and definitely, my column name is not * and I'm using some third-party API to quote the SQL queries which produce the above result for me which is not correct. treyBake Qirel I'm still puzzled why it's working in the newer versions and not in the older one – aMoL Thite Apr 25 '19 at 11:30
  • Its quite interesting, because (as you've said), it works in newer versions of MySQL (somewhere between 5.6 and 5.7). My guess is that the newer versions have been made more clever, and would know that `SELECT * FROM..` would always be the *select everything* operation, even if its quoted `\`*\``. Interesting indeed. Couldn't find any documented changes on this (but its probably mentioned somewhere). – Qirel Apr 25 '19 at 11:35
  • Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) – tripleee Apr 25 '19 at 11:38

1 Answers1

2

Short answer: No need to wrap your * in ticks - it can in fact be misleading, or cause errors by doing so.

From the MySQL manual,

The identifier quote character is the backtick (`)

This basically means that anything quoted with a backtick, is to be interpreted as a column or a table.

* is an operator - not a table or a column. This operator basically says give me all the columns for this table. When you do...

`*`

..you are basically asking MySQL to grab you the value of a column with the exact name of *, instead of using the actual operator * which would give you all columns. 

SELECT * FROM `myTable`   -- Selects all columns
SELECT `*` FROM `myTable` -- Tries to select the column with the actual name of *

Obviously having a column named * would be a very bad idea.

It's quite interesting, as something has changed between version 5.6 and 5.7 (see fiddles for MySQL 5.6 and MySQL 5.7), which allows for the following expression to be valid, although I wasn't able to find it documented anywhere.

SELECT `*` FROM `myTable`
Qirel
  • 25,449
  • 7
  • 45
  • 62