Can a MySQL SELECT statement work without specifying column names?

Question

How do I SELECT rows and return all the columns, instead of specifying individual column names?

Can I also return only columns that match a specific criteria? (eg. col names beginning with an "_")

Answer 1

SELECT * FROM TABLE_NAME selects all columns.

Answer 2

You can select all columns with:

SELECT * FROM table_name

You cannot filter on column names in a SELECT statement, but you can filter on the column names as they exist in the database themselves with SHOW COLUMNS:

SHOW COLUMNS FROM table_name LIKE "_%"

Which will display columns that start with '_'. If you have a very large table with very many columns, and you must build a SELECT statement out of those fields, I would suggest programmatically building the statement by first displaying the columns with SHOW COLUMNS, then constructing your SELECT query out of that result.

Answer 3

Everything:

SELECT * FROM mytable

First select needed columns (beginning with col...) from database schema table:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'mytable' AND column_name LIKE 'col%'

then select values:

SELECT [found column names from previous query] FROM mytable

Answer 4

As you are trying to use the field names as if they were data, it's obvious that your table design is not right for what you want to do.

Instead of a table where you have data in the field names, like:

Id  English  Swedish  German
-------------------------------
1   Water    Vatten   Wasser
2   Car      Bil     Auto

You want a table where you have the data in the fields:

Id  Language  Word
-------------------
1   English   Water
1   Swedish   Vatten
1   German    Wasser
2   English   Car
2   Swedish   Bil
2   German    Auto

From a table like this there is no problem getting specific languages dynamically, something that can't be done with the first table without generating the SQL query dynamically.

In the normalised form you would use two tables:

LanguageId  Language
---------------------
1           English
2           Swedish
3           German

Id  LanguageId  Word
------------------------
1   1           Water
1   2           Vatten
1   3           Wasser
2   1           Car
2   2           Bil
2   3           Auto