use 'not like' in mySQL show statements

Question

I have a dropdownlist which is being populated by column names based on the table name selected by user in the previous dropdown. I am using the following query

SHOW columns from abcTableName LIKE '%name' which works

I want all to include all the columns names except a few columns. Therefore , I want the query like this

SHOW columns from abcTable NOT LIKE ('%name','%pk','%fk')  

which does not work . Even

SHOW columns from abcTable NOT LIKE '%name'  

does not work

Currently I run two loops to fetch the columns names- outer loop to pass the table name and inner loop to pass the parameters to the query which takes a lot of time .I want to optimize it.

Can anyone please suggest ?

Answer 1

You could use a more formal method:

SELECT COLUMN_NAME
FROM information_schema.columns
WHERE 
    table_schema = '[database]' AND
    table_name = '[table_name]' AND
    COLUMN_NAME LIKE '%name' AND
    COLUMN_NAME NOT LIKE '%pk' AND
    COLUMN_NAME NOT LIKE '%fk';

Answer 2

use where clause

SHOW columns from abcTable where field not like '%name'  

look at Extensions to SHOW Statements https://dev.mysql.com/doc/refman/8.0/en/extended-show.html