select all fields with common start phrase in MySQL

Question

I have a table with 77 fields in MySQL. Quite a number of them start with the same prefix "mt3_". Instead of writing them all out, I wanted to use this syntax to select only those fields from the table that start with "mt3_":

SELECT tablename.mt3_* from tablename;

However this does not work... What is the correct syntax to do this?

Thanks.

There is none. Either select all columns or name the ones you want to select — juergen d, Oct 22 '14 at 01:03

score 1 · Accepted Answer · answered Oct 22 '14 at 01:13

1

You can't do this directly, but you can ask MySQL for the column names in that table and filter with wild card character % in a WHERE statement, like this;

SELECT column_name FROM information_schema.columns 
WHERE table_name = 'tablename'
AND column_name LIKE 'mt3_%'

You can then run another query with a loop of these results (I'm guessing you're using PHP and mysqli);

while($row = $result->fetch_assoc()) {
     $cols[] = $row['column_name'];
}

$colnames = explode(",",$cols);

$sql = "SELECT $colnames FROM tablename"

Hope this helps.

answered Oct 22 '14 at 01:13

worldofjr

3,868
8
37
49

I'm not using PHP but Python, although in this work I'm doing everything just with mysql directly. I understand the first part. Can I use the second part too in mysql, or how would I go about that -with PHP I guess...? – oaklander114 Oct 22 '14 at 02:36
Ah Python ... `"SELECT * FROM tablename WHERE " + ' '.join(['%s = 1' % name for name in columns])` – worldofjr Oct 22 '14 at 02:43
I don't think it's possible without using some sort of procedural language (or just typing the column names in). – worldofjr Oct 22 '14 at 02:58

score 1 · Answer 2 · edited May 23 '17 at 11:57

There is no way to do that without dynamic SQL and honestly I can not recommend doing something like this.

If you care enough to SELECT only certain fields then you also probably care enough about best practices and it is a best practice to specify all the fields you want inside a SELECT query.

Use SELECT * for a single fast retrieval. If you are actually coding something then take the time to specify the fields you want.

select all fields with common start phrase in MySQL

2 Answers2