CODEWITHSUNDEEP
mysql
divHelper11
divHelper11

Reputation: 2208

Get column length of mysql table

I am trying to get each column's name, type and length of mysql table.

SELECT column_name, data_type, column_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = "test_table"

It returns for example:

column1
int
int(11)

column2
varchar
varchar(255)

How do I get the column's length properly? I expect something like this:

column1
int
11

column2
varchar
255

I would need to trim the returned string from column_type to receive this 11 length. Is there no other way? I also found character_maximum_length but it gives different results (I guess in bytes).

Upvotes: 0

Views: 487

Answers (1)

Jared Dunham
Jared Dunham

Reputation: 1527

For max length in characters, use: CHARACTER_MAXIMUM_LENGTH

For max length in bytes, use: CHARACTER_OCTET_LENGTH

The query should look like:

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = "test_table"

If the value is -1, it's a varchar(max) or nvarchar(max) field.

A null value is a number that doesn't have a length.

Upvotes: 1

Related Questions