Solved

The int length of MysQL

Posted on 2014-01-02
15
673 Views
Last Modified: 2014-01-07
Dear all,

I tried to find out, for each of the column, the type of it and the length of it for all tables
by using:

select  distinct COLUMN_TYPE,  DATA_TYPE, table_name, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, numeric_scale
 FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = '<Database>' and DATA_TYPE= 'int' 
order by table_name

Open in new window


but it seems that only the int type DO NOT have the length indicated by how long is the INT type.

By wanting to know the HOW LONG is the int field type, like int (10) , int (4) and so on, which table should I look for?

it is funny that only INT type don't record the type but I don't believe, there must be a table store this information but I can't see why !!

Or please suggest what should I do to change the query about to display how long is the int type.

I will change the query to a bit different than the above to check all column's type and length of a table one by one:

 select COLUMN_NAME,COLUMN_TYPE, IS_NULLABLE, Column_default, COLUMN_COMMENT, 
 DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, numeric_scale
 FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = '<database>' AND TABLE_NAME = '<table>' ; 

Open in new window

0
Comment
Question by:marrowyung
  • 9
  • 6
15 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
For INT, and other numeric types, the NUMERIC_PRECISION and NUMERIC_SCALE columns should have the information you are looking for.  Note: the INT(10) and INT(4) controls the display width, which you could use with ZEROFILL.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I knew what you mean, but it seems it is only true for bigint, I have a result from the select statement that show a column has type int(6) but the number_precision is 10 and numeric_scale is 0, do you know where it get the 6 from ?
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
The 6 was defined in the design of the table.  My experience is you can still INSERT an integer larger than 6 digits into the INT(6) field.  However, you will get a failure with 11 digits because an integer has a maximum value 2,147,483,647 and 4,294,967,295 for signed and unsigned, respectively.  Both values are 10 digits.  If you need more control, use NUMERIC/DECIMAL.  For example, NUMERIC(6,0) essentially is a 6-digit integer as you have zero decimal places.  When you query the information schema, you will see NUMERIC_PRECISION equals 6.

REFMAN:
http://dev.mysql.com/doc/refman/5.0/en/integer-types.html
http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
no, what I need is to check out the field lenght accuratlely, that's it!

But it seems using the using the INFORMATION_SCHEMA.COLUMNS  can't return information well.

you can try my script and see if that column ALWAYS return the right int length for you ! for my case, only 1 case is correct ! but not for int.

All other type can query the INFORMATION_SCHEMA.COLUMNS  and get thing right !
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
An INT's length is 10, which is what NUMERIC_PRECISION states; therefore, yes, the information returns "right" for me.  The problem here is you want the display width, which is in the COLUMN_TYPE definition.  This is not the same thing as the maximum length of the field.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
If you really need this information, you can just code for it like so.  You can use a CASE statement on DATA_TYPE, so you show something else for types other than int.  This way you can have one column with your defined length.
select  distinct COLUMN_TYPE,  DATA_TYPE, table_name, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, numeric_scale
     , substring(COLUMN_TYPE, 5, instr(COLUMN_TYPE, ')')-5) AS COLUMN_WIDTH
 FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'test' and DATA_TYPE= 'int' 
order by table_name

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"The problem here is you want the display width, which is in the COLUMN_TYPE definition.  This is not the same thing as the maximum length of the field. "

ok, but this is very different the REST of the type, agree?

"You can use a CASE statement on DATA_TYPE, so you show something else for types other than int."

don't understand what you mean by show something else for type other than int? I need the length of int, like I want like to detect if int(6) was changed to int(10), then a field should show me the 6 has changed to number 10.

I only need this, but why I need this:

substring(COLUMN_TYPE, 5, instr(COLUMN_TYPE, ')')-5) AS COLUMN_WIDTH

Open in new window


?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
"You can use a CASE statement on DATA_TYPE, so you show something else for types other than int."

don't understand what you mean by show something else for type other than int?
If you end up running one query to get the different data types at once, you may want one column with length.  Therefore, you could use:

CASE DATA_TYPE WHEN 'int' THEN ... ELSE ... END

Anyway, it sounds like you only want to do this for INTs, so no worries.

I only need this, but why I need this:
I was trying to explain this earlier...the length of the INT to the database is 10, which is the value in the NUMERIC_PRECISION.  MySQL, unlike other database systems, allows you to specify a display width for integer data types.  It works with the ZEROFILL, so that you can display 12 characters as 001234567890.  However, it does not change the maximum value length of the field.

Therefore, to get the information you want, you have to look in a different place than the NUMERIC_PRECISION column.  In the information schema, it provides you with the column type definition, which has the display width.  Using the code I provided, you parse the information you need from there.

I need the length of int, like I want like to detect if int(6) was changed to int(10), then a field should show me the 6 has changed to number 10.

By the way, are you looking for when the user inputs data in the table larger than the defined INT(6)?  If so, you will need to find MAX(LENGTH(int_column)).  You can compare that with the display width you get with the code I provided above.

Is that more clear?

Kevin
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"If you end up running one query to get the different data types at once, you may want one column with length.  Therefore, you could use:

CASE DATA_TYPE WHEN 'int' THEN ... ELSE ... END

Anyway, it sounds like you only want to do this for INTs, so no worries. "'

you are quite right that I will use some kind of check like this for all column and data type, but I will use IF and ELSEIF.

so CASE is better and easier ?

No. I will do it for all type for each column for each table scan/return by cursor. And the logic is: if the type length is longer than the same field in other table, then it will


"Therefore, to get the information you want, you have to look in a different place than the NUMERIC_PRECISION column.  In the information schema, it provides you with the column type definition, which has the display width.  Using the code I provided, you parse the information you need from there."

it seems it is the only method, I accept. so the substring(), just like a parser to break the string apart and let you get THAT information ?

I tried the varchar case by your suggestion:

select  distinct COLUMN_TYPE,  DATA_TYPE, table_name, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, numeric_scale
     , substring(COLUMN_TYPE, 5, instr(COLUMN_TYPE, ')')-5) AS COLUMN_WIDTH
 FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'test' and DATA_TYPE= 'varchar'
order by table_name 

Open in new window


the column_width is har(100, so for this one, this means the substring number, the 5 and -5  in your example, has to change for all type, right? it doesn't works for bigint too !

e.g. bigint should be : substring(COLUMN_TYPE, 8, instr(COLUMN_TYPE, ')')-8)

"By the way, are you looking for when the user inputs data in the table larger than the defined INT(6)?  "

Right now we have a separate DB for store BEFORE DELETE, after insert/update trigger data and on the source DB, we would like to detect, table by table that, if any field has extend any field length, we would like to generate the respect alter table xxx modify column xxx column_type so that both source and separate DB's filed has the same length.

is it very hard to do?



"MySQL, unlike other database systems, allows you to specify a display width for integer data types.  "

so you agree that MYSQL only don't handle this correctly, right?
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Yes, other database systems use just the precision and scale for numeric fields, so they do not run into the issue MySQL does.  For character data types, note that the max character length column accurately shows the defined max characters; therefore, you do not need to parse the column type string.  However, you are correct, the 5 is what would change.  I hard-coded it, since you only need this for INT types, so we are subtracting the 5 characters "INT()" from the substring().

Regarding CASE versus IF, MySQL has IF syntax whereas other systems do not.  i.e., CASE is a more portable syntax; therefore, it first popped in my mind.  You can use an IF statement.

For example, you could do something like this:
IF(DATA_TYPE = 'int', 
  substring(COLUMN_TYPE, 5, instr(COLUMN_TYPE, ')')-5), 
  CHARACTER_MAXIMUM_LENGTH
)

Open in new window


The other benefit to CASE is multiple WHEN conditions, which are similar to nesting IF statements of SWITCH clause in other programming languages.
REFMAN: http://dev.mysql.com/doc/refman/5.0/en/case.html

Hence, you can get the value for each data type in one column by going through each type case by case.

CASE 
  WHEN DATA_TYPE = 'int' 
    THEN substring(COLUMN_TYPE, 5, instr(COLUMN_TYPE, ')')-5)
  WHEN DATA_TYPE IN ('numeric','decimal') THEN NUMERIC_PRECISION
  /* other WHEN statements you want */
  ELSE CHARACTER_MAXIMUM_LENGTH -- default
END

Open in new window


Kevin
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I want to do mulitple IF statement, like if the type is bigint, if type is varchar and so on then  I will compare the length of it.

once it is compared, then I will do 3 case:

1) if the lenght is shorter, then I will change the table name in other DB.
2) if the lenght is longer, then I will issue alter statement to modify the same field with the updated lenght on the smae table in other DB.
3) if the type of that field has been altered. then I will change the table name in other DB too.

so some examplke you can share is ?
0
 
LVL 1

Author Closing Comment

by:marrowyung
Comment Utility
Very excellet, this is what I want !! please watch me out for more MySQL question.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
for example, please help to answer this:

http://www.experts-exchange.com/Database/MySQL/Q_28331214.html
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now