Morgan Tocker ([info]mtocker) wrote,
@ 2007-12-17 22:12:00
Previous Entry  Add to memories!  Tell a Friend!  Next Entry
Entry tags:mysql, php

Just to confuse you...
Take a look at these two numbers:


CREATE TABLE my_table (
number_1 BIGINT(4),
number_2 INT (10)
);


Which one do you think takes up less space? The answer is that BIGINT always takes 8 bytes and that INT always takes 4 bytes. The value in the parenthesis has nothing to do with storage. The only use I am aware of that it has, is with the ZEROFILL feature (which I never use):


mysql> CREATE TABLE a (a INT(5) ZEROFILL);
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO a VALUES (1);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM a;
+-------+
| a |
+-------+
| 00001 |
+-------+
1 row in set (0.00 sec)


It seems that there are a lot of open source packages with INT(4) (or similar). If you only need 4 digits, you probably want a SMALLINT ;)


(Post a new comment)

here via planetmysql
[info]burr86
2007-12-18 07:39 am UTC (link)
I've always wondered, but never taken the time to figure that out. Neat!

(Reply to this)

Legacy datatype syntax
(Anonymous)
2007-12-18 11:05 am UTC (link)
Morgan, good point! You mentioned part of the ugly bit, but not all of it though. As I understand it, the number in parentheses denotes the "display width" of the column.

Now this has always been one of my MySQL wtf's: Why are attributes that have to do with *displaying* data part of the definition of the data type? Zerofill is in the same league - it means *nothing* - that is, nothing other than that the mysql client adds zeros in front. Yay :(

(Side question: why does something like SHOW CREATE TABLE add the damn stuff when I didn't specify myself in the DDL?)

In MySQL, the variety of data types is very much *storage* oriented - more that than anything else. I mean, sure, in an application it makes sense to think about "domains" rather than just "data types" - that is, it can make sense to have metadata to describe the hard (storage type, nullability) as well as the soft (specific validation, display width, value editor, internationalization data) properties of an item together. But it does not make sense to do so on the database level. The database does not and cannot know what applications we will be building against it - so it can never provide the full range of metadata properties. Therefore it does not make sense to make these attributes part of the column definition at the DDL level.

(Of course - I am not dismissing the case where the *application uses the database* to store its metadata - that is a good use but in that case the application is in control of its own metadata again.)

I can imagine that a limited set of extended attributes that pertain to the handling of *data* can be useful. For example, I think it could make sense to allow a 'regex' attribute in order to allow validation of values. But that is slippery already - a regex is just a special case of checking the structure of text, and in that case, why would we include regex support but exclude full-fledged BNF syntaxis or XML Schema's?

(Reply to this) (Thread)
Re: Legacy datatype syntax
[info]mtocker
2007-12-18 02:32 pm UTC (link)
> (Side question: why does something like SHOW CREATE TABLE add the damn stuff when I didn't specify myself in the DDL?)

I've always assumed this has to do with the way we store the DDL in a .frm file, and that the information is lost and can't be recreated. But yeah, the way it shows INT(11) and not INT annoys me too!

> In MySQL, the variety of data types is very much *storage* oriented - more that than anything else. I mean, sure,
> in an application it makes sense to think about "domains" rather than just "data types" [..]

Interesting observation. I've always hoped that we would implement *more* datatypes. We all know that an IPv4 IP address can fit into an INT, but why must I use INET_ATON() and INET_NTOA()? Temporal columns end up storing values on disk in a numeric type, so this isn't too much different.

> I can imagine that a limited set of extended attributes that pertain to the handling of *data* can be useful. For > example, I think it could make sense to allow a 'regex' attribute in order to allow validation of values

This sounds a little bit like Check Constraints (not in MySQL)

- Morgan

(Reply to this) (Parent)(Thread)
Re: Legacy datatype syntax
(Anonymous)
2007-12-18 11:49 pm UTC (link)
"Interesting observation. I've always hoped that we would implement *more* datatypes. We all know that an IPv4 IP address can fit into an INT, but why must I use INET_ATON() and INET_NTOA()?"

Well, the problem with "more datatypes" is that you can never have enough of them - not in this way. I mean, although it is often used and makes sense for many applications, IPv4 is still a specific to a particular kind of application. That is to say, you cannot think of an IPv4 data type (should it exist) without thinking about its application: to represent IP addresses. How different is that with the data types we have in MySQL! Those are practically all abstract in the sense that they could represent anyting (except of course the GIS datatypes).

Now, if we keep those abstract data types in mind, say, the integer family...well, how do we choose between TINYINT, SMALLINT, INT etc? Usually that is based on storage capacity - not anything semantical. Not that those are unrelated - a particular semantics sets the boundaries for the storage of course. But my point is that it would be far better if we have

1) less true data types (let the db figure out the storage details)
2) the ability to define domains, and better describe the constraints of the data that should be allowed to be stored in the column that uses that domain.

So, put simply, if I had my way, there would be no "tinyint", "smallint" etc. but just one abstract INTEGER. To use it, we'd have to create a domain that would allow me to define the minimum and maximum value, nullability etc - things the db can check for me. Then the db can worry al it likes about whether to store it in one, two or three bytes etc.

Right now, we abuse the storage properties to obtain a value restriction effect.

Another thing is CHAR vs VARCHAR vs TEXT. Ideally, the db should decide if it uses padding and fixed or variable formats, and whether it stores it inline or outline with the table. All those things are implementation details that have no bearing on the meaning of the data

-Roland (2nd comment is mine too btw in case someone wants to slap me ;p)

(Reply to this) (Parent)
Not necessarily...
(Anonymous)
2007-12-18 03:46 pm UTC (link)
Hi Morgan!

"The answer is that BIGINT always takes 8 bytes and that INT always takes 4 bytes."

Well, your point about being aware of actual storage requirements is spot-on. However, the above statement is not necessarily true. Internally, the different storage engines use compression techniques to reduce the storage space needed for various data types, including integers. So, while your point is correct, it's not always true that a BIGINT will always take up 8 bytes... :)

-jay

(Reply to this) (Thread)
Re: Not necessarily...
[info]mtocker
2007-12-18 03:51 pm UTC (link)
Fair point. In GA releases it's true (w/bundled storage engines). Are you referring to Jim's "number" concept in Falcon?

(Reply to this) (Parent)(Thread)
it's in the metadata
(Anonymous)
2007-12-18 06:07 pm UTC (link)
The actual difference between INT and INT(5) is in the resultset metadata. Start mysql client with -T option, and pay attention to, the "Max_length" and "Length" attributes of the row when selecting from both tables..

So, INT(X) *without* zerofill does serve a purpose. It sets the Length to X in metadata.

(Reply to this) (Parent)(Thread)
Re: it's in the metadata
[info]mtocker
2007-12-18 06:10 pm UTC (link)
Ahh, thank you ;)

(Reply to this) (Parent)
FREE 1 GB Resell Rights eGoods as bonus?
(Anonymous)
2007-12-19 08:51 pm UTC (link)
web-packages.com

Download FREE 548 ebooks from 19 categories!
Among them:

+ Arts
+ Automobiles
+ Business
+ Computers
+ Education
+ Fiction
+ Games
+ Health & Beauty
+ Home & Family
+ Internet Marketing
+ Kids & Teens
+ Music
+ Real Estate
+ Reference
+ Self-Improvement
+ Shopping
+ Society
+ Sports
+ Travel & Recreation

Also much more Web Sites, Scripts and a lot of SOFT for you site!

Visit NOW!

web-packages.com




(Reply to this)

YKJzrGZHKHVvRLm
(Anonymous)
2008-06-04 01:00 am UTC (link)
aLSsp6 sd95vbm24r7mvu4091rvbqp

(Reply to this)

Create an Account
Forgot your login or password?
Login w/ OpenID
English • Español • Deutsch • Русский…