Scott says:

I always thought that was weird too. However, in developing an app for ACH processing I discovered a data specification that required zerofill left to an exact number of digits. So a financial transaction record may require 9 spaces of data for an amount. The final 2 spaces are cents and the prior 7 are dollars. So a transaction amount of $1,877.99 would be written in the ACH file as "000187799". If you set up the MySQL data type to store and output in this manner is saves steps getting the data ready for writing in its final state.

6/3/10 1:17 AM

Jules says:

I like to use COALESCE(fieldName,0) which turns NULL into a zero. Great for avoiding cf errors in numberFormat() and dollarFormat().

6/3/10 3:12 AM

Anonymous says:

INT(size) is very useful. And, as far as I understand, it directly affects the size of your database table. It also allows for more useful data - I'll show how below.

Just to be clear, the size is the width of the data displayed, which for a small number is left-padded with either spaces or zeros, as another commenter explained. The usage of TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT designates how much storage space is reserved for your data. Using a default value of NULL can help some, but I'm not sure how much it affects the overall table size. This is a big deal.

Lets say you have a table with twenty columns, all of type INT. Now lets say that on average 15 out of the 20 columns are left with the default value of zero. The database still has to reserve space for the larges number possible even if the actual value is a tiny number. That reserved space happens when each new row is created for the table. This is how I understand the MySQL database works. If I am wrong, please correct me.

From the Docs a regular INT can be any number between -2147483647 and 2147483647, or UNSIGNED form 0 to 4294967295. Notice that's 10 characters unless your using a negative, then you need eleven. Most of the time, SMALLINT or MEDIUMINT will suffice. But, primary keys with auto-increment default to INT(11).

MySQL does not have a datatype for BIT as MSSQL does. I always wished MySQL had this, but I discovered it does... sort of. If you do a TINYINT(1) UNSIGNED it only allows a 1 or a zero. Any number entered that is not a zero becomes a one. Nice trick, eh?

Side note on that, I discovered an oddity with this BIT trick. If you insert a 2 into an UNSIGNED INTYINT(1) slot it returns as a 1 as I stated earlier. Then alter the table to update the column to a TININT(2) or something larger then it magically appears as a 2. Interesting... The original inserted value (up to the TINYINT limit of +/-127 or UNSIGNED 255) is not lost. The SIZE of 1 only changes how the value is returned or displayed.

6/3/10 4:12 PM

Anonymouse says:

Another reason for it's popularity is it's special functions. Research the GROUP_CONCAT() function. In certain circumstances it's absolutely magical, heaven-sent. No other brand database can come close in comparison with the extensive special functions that MySQL provides. I use FIND_IN_SET(), FIELD(), and MAKE_SET() functions all the time. Trying to do these types of things in MSSQL or Oracle was like pulling out fingernails.

6/3/10 4:17 PM

Tom de Manincor says:

@Anonymous - Check out the link I provided in the original post. It talks about what you mention and shows how it doesn't actually do what you expect.

I couldn't believe that the SIZE on the INT field had nothing to do with database storage. I tested it myself to verify an INT(4) and an INT(10) have the same range. And it does not affect how many characters are actually displayed either.

It's only purpose is for the padding of ZEROs on a ZEROFILL. Which I found hard to believe.

@Anonymouse - GROUP_CONCAT() is my favorite MySQL feature. It does wonders for the Coldbox Security Interceptor when pulling rules from the database. I'm not that familiar with the others you mentioned, I will check those out.

Thanks for the feedback.

6/3/10 5:07 PM

Colin says:

Hmm, I'm not convinced.

I created a table of three INT id fields, and populated it with about 84M rows. The table file size was 1.1Gb. In an effort to make it smaller I changed the field types to SMALLINT, and the file size went down to 563Mb; the index file reduced in size to.

I hope that helps, or maybe it just adds to the confusion.

7/8/10 9:36 PM

Colin says:

I just realised that that's not exactly what you're talking about though. If I had changed them to INT(5), or something, that would have been a more valid example? I think the previous comment that mentioned the different INT types confused me.

7/8/10 9:41 PM