10

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 thank u very much in advance.

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

Marcin Nabiałek
  • 109,655
  • 42
  • 258
  • 291
martinwang1985
  • 528
  • 3
  • 26
  • 42

4 Answers4

19

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • hi, thank u very much for ur kindness reply. but i'd like to know,if i dont want use "like",is it possible to use "="? – martinwang1985 Jul 31 '14 at 10:21
  • also, i'd like to say ,why cannot i use "SHOW COLUMNS FROM table = 'colmunname'":thank u very much – martinwang1985 Jul 31 '14 at 10:24
  • Because that's not the syntax. See the syntax that VMai copied from the documentation. If you want to use something other than `LIKE`, you have to use `WHERE` with an expression. – Barmar Jul 31 '14 at 10:27
  • thank u. but i have tried "show columns from table set where filed= 'Id'" or ""show columns from table where filed= 'Id'"".i was told an error.could u pls help me if there is a correct way? thank u very much for ur kindness help again – martinwang1985 Jul 31 '14 at 10:36
  • Because you misspelled `field`. – Barmar Jul 31 '14 at 10:38
  • i know .its show columns from table where field='Id'".thank u very much for ur kindness help.best regards – martinwang1985 Jul 31 '14 at 10:38
  • I know that, that's what I wrote in my answer. – Barmar Jul 31 '14 at 10:39
0

SHOW ... LIKE behaves like the use of the LIKE operator in the WHERE clause of a common query:

SHOW COLUMNS Syntax

SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

SHOW COLUMNS displays information about the columns in a given table. It also works for views. The LIKE clause, if present, indicates which column names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 21.32, “Extensions to SHOW Statements”.

Emphasis by me.

VMai
  • 10,156
  • 9
  • 25
  • 34
0

Where is used to get an exact match where as like is used to get a wider range of columns using wildcards.

SHOW COLUMNS FROM table like 'my%";

The above will find all columns that begin with my.

Where as with a where clause you cannot use wildcards and will only get returned exact matches. Obviously you could use where with between,> or < etc in other cases which would give you a wider range but still not allow you to use any wildcards.

Varun Nath
  • 5,570
  • 3
  • 23
  • 39
0

maybe this: SHOW COLUMNS FROM accounts LIKE'id'

ilgam
  • 4,092
  • 1
  • 35
  • 28