Obtaining Table metadata from mysql in java

Question

Is there a way to get table metadata before first executing select query on the table?

Restated: Is there a way to get the column size of a specific row and column of data for the numeric types: int, tinyint, bigint, etc?

DatabaseMetaData md = connection.getMetaData();
ResultSet rs = dbmd.getColumns(null, null, tableName, "%");

I can't use the above code as the COLUMN_SIZE for numeric type(int,tinyint,bigint,etc) will return the maximum allowed size for these types. I need to get the columnDisplaySize.

The above code will return 10 for int(8), but I need 8 which I can get from resultMetaData of a table.

So is there a way to do this without running a select statement first.

Thanks

Solution 1

You may want to check into what the INFORMATION_SCHEMA table within MySQL is capable of giving you. Sometimes you can use them to your advantage. I did some quick looking and nothing hit me immediately from within there.

MySQL Information Schema: http://dev.mysql.com/doc/refman/5.6/en/information-schema.html

Solution 2

As @technocrat said, check this query on information_schema

SELECT column_name, column_type FROM INFORMATION_SCHEMA.columns where table_schema='[Table schema]' and table_name='[Table Name]'

Solution 3

I may be off the mark with what you're asking but is mysql command show columns in <tablename> of any use? http://dev.mysql.com/doc/refman/5.1/en/show-columns.html for more info.

Solution 4

In the case of INT(8), you should know that the 8 is not a size limit. That's a frequent misconception by MySQL users. The argument to INT-like data types is only a hint to SQL to pad the values if they have fewer digits than the argument. It's normally used only for ZEROFILL.

CREATE TABLE foo ( i1 INT(8) ZEROFILL, i2 INT(16) ZEROFILL );
INSERT INTO foo VALUES (1234, 1234);
SELECT * FROM foo;
+----------+------------------+
| i1       | i2               |
+----------+------------------+
| 00001234 | 0000000000001234 | 
+----------+------------------+

The value 8 vs. 16 does not change the storage space required by an INT, nor the range of values it supports. The INT data type is always 32-bits, and always allows values from -2³¹ to 2³¹-1, regardless of the argument you give it.

Likewise TINYINT is always 8 bits, SMALLINT is always 16 bits, MEDIUMINT is always 24 bits, and BIGINT is always 64 bits.

The 10 value you get is the numeric precision, not a size limit. That is, a 32-bit value may use up to 10 digits when displayed in base-10:

SELECT LENGTH(POW(2,31));
+-------------------+
| LENGTH(POW(2,31)) |
+-------------------+
|                10 | 
+-------------------+

View more solutions