0

I don't know if its possible, but I want to get the length of a datatype in MySQL.

enter image description here

So the type is a int(11) and I know how to get the type back with the query:

SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS

it will return a list of 

[
  int, varchar
]

but now I want to get the length of the int back. So what I want to achieve is this:

[ int =>  11, varchar  =>  45]

enter image description here

To make it more clear. if i edit the structure of the table. You will see this page. i want to get the first 3 columns of this table structure. This if for frontend purposes.

Please suggest me a query to get this result?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Eric
  • 361
  • 6
  • 25
  • Does this answer your question? [What is the size of column of int(11) in mysql in bytes?](https://stackoverflow.com/questions/5634104/what-is-the-size-of-column-of-int11-in-mysql-in-bytes) – P.Salmon Jan 16 '20 at 10:19
  • hi, no it doesn't i changed the question to make it more clear – Eric Jan 16 '20 at 10:25
  • Use this to find the `SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = "bigint"` to get the total integer filed. – Santosh Aryal Jan 16 '20 at 10:29

1 Answers1

2

INFORMATION_SCHEMA.COLUMNS has a lot of information. Please see example here: https://www.db-fiddle.com/f/umz2GYQ6U3XEpcTjFs8nYg/0

So, you should select not only DATA_TYPE. You can use COLUMN_TYPE or CHARACTER_MAXIMUM_LENGTH.

TemaTre
  • 1,422
  • 2
  • 12
  • 20
  • thnx, that really helped a lot. do you have a query that gets the name, type and max_lenght of all table headers in a table? – Eric Jan 16 '20 at 10:29
  • The CHARACTER_MAXIMUM_LENGTH is only about VARCHAR columns. For INTEGER columns look the article https://nexladder.com/blog/what-does-int11-means-in-mysql/ – Slava Rozhnev Jan 16 '20 at 11:06