mysqlsql

MySQL query "SHOW COLUMNS FROM table like 'columnname'"

I have a question about SHOW COLUMNS FROM table like 'column name'". I have already tried some tests for some times, it seems be similar like "where column name='column'.

However, I just would like to confirm

Also, I'd like to say, why can I not use SHOW COLUMNS FROM table = 'columnname'?

Solution

  • It's more like

    WHERE column_name LIKE 'column name'

    Since it uses LIKE, you can put wildcard patterns in the parameter, e.g.

    SHOW COLUMNS FROM table LIKE '%id'

    will find all columns that end in id.

    If there are no wildcard characters, then LIKE is equivalent to =.

    If you don't want to use LIKE, you can use WHERE:

    SHOW COLUMNS FROM table WHERE field = 'column name';

    In the SHOW COLUMNS output, the field column contains the column names. The WHERE clause also permits testing other attributes, e.g.

    SHOW COLUMNS FROM table WHERE type LIKE 'varchar%'

    will find all VARCHAR columns.