Geek Answers Handbook

MySQL wildcard in favorites

Is there a way to select wildcard columns.

as

to select columns with names of type maybe ' SELECT %type% from table_name '?

+2
mysql wildcard
source share
3 answers

Not really. You can use the wildcard character * to select all columns. If you join multiple tables, you can select all the columns from a specific table, the prefix * with the table name or alias:

 SELECT a.id, a.title, b.* FROM articles AS a JOIN blurbs AS b ON a.id = b.article

However, you should not use * unless you write a database administration program.

Alternatively, you can create a statement in SQL or another language by extracting table metadata to get the column names. Using only MySQL, you can query the COLUMNS table in INFORMATION_SCHEMA to get the column names and use GROUP_CONCAT to create a list of columns for the statement.

 SELECT CONCAT( 'SELECT ', GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '), ' FROM ', :db, '.', :table, ' WHERE ...' ) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=:db AND TABLE_NAME=:table

Replace ": db", ": table" and "..." with the corresponding values. You can even turn it into a prepared expression so that you can use it for any table. From there, PREPARE and EXECUTE constructed statement.

If you are not limited to SQL for programming, it should be less messy. The DB driver for your language of choice probably offers methods for retrieving metadata. The actual implementation will look like a pure SQL approach (get column names, build command, prepare, execute), but should not be so ugly, because you will use an algorithmic rather than declarative language.

I would be very interested to see that this is really necessary.

+3
source share

You can find all fields containing the type inside the name using information_schema, and then using the prepared statement.

 set @str = (concat('select ',(select concat(group_concat(column_name),' from ',table_name) from information_schema.columns where table_schema = 'your_db_name' and table_name = 'your_table_name' and column_name like '%type%'))); prepare stmt from @str; execute stmt; deallocate prepare stmt;
+1
source share

Of course, it is possible if you use the external interface. If php just uses

  $fieldlist= "cola, colb "; $tablename="tabl"; "select $fieldlist from $table"

My intuition says that you are doing something simple with php-mysql, but I could be wrong.

0
source share

More articles:


All Articles