Eric Bergen

MySQL has a little know feature for numerical types known as zerofill. This feature effects the display size of numerical types. Unlike the string types the number inside the parentheses is not the storage size in characters for the type. For numerical types the type name itself determines storage size.

The confusion between types comes from the number inside parentheses for different types. The integer type it’s the padding size for zerofill. The following examples demonstrates zerofill. All of these tables store the same range of values since they are all integer type.

Zerofill with padding specified:

mysql> create table t (t int(3) zerofill);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t set t = 10;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+——+
| t |
+——+
| 010 |
+——+
1 row in set (0.11 sec)

Zerofill with default width, the same as int(10):

mysql> create table t (t int zerofill);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t set t = 10;
Query OK, 1 row affected (0.02 sec)

mysql> select * from t;
+————+
| t |
+————+
| 0000000010 |
+————+
1 row in set (0.08 sec)

Without zerofill:

mysql> create table t (t int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t set t = 10;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+——+
| t |
+——+
| 10 |
+——+
1 row in set (0.00 sec)

One common usage for this is creating invoice ids. It saves the work of having to use lpad() for ids like this ‘UP000009′.

I’m writing this listening to music from my treo synced through vmware running windows. The only real snag I ran into was keeping linux from taking over the usb device before windows could. The quick solution I came up with was to leave this running in the background:
watch -n0 "sudo rmmod visor usbserial"
I’m sure there is a better /etc/modules method but for now this works. The first band synced was Clutch

My bike rides to work and gym time will be much more entertaining now and I won’t have to spend money on a music player. Details on battery life will be in a future post.

I often hear complaints about how bad the MySQL manual’s native search functions are. Wes recently posted an entry on using firefox key word searching to solve the problem. This is such a great post I have decided to share it with all of you. I also want to add that the MySQL’s questionable site search can be avoided all together by using yahoo’s site search. To use this search URL goto Bookmarks -> Manage Bookmarks. Navigate to your Wes style bookmark. Change the Location to this url:

http://search.yahoo.com/search?_adv_prop=web&ei=UTF-8&vs=dev.mysql.com&vl=lang_en&fl=1&va=%s

Now your keyword search will use yahoo to return more relevant pages only in english. If you want to use a different language change lang_en in the url to the language you prefer. Thanks Wes!