List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:March 16 2006 8:38pm
Subject:Re: NULL columns
View as plain text  
Hi!

----- Original Message ----- 
From: ""Martijn Tonies"" <m.tonies@stripped>
Newsgroups: mailing.database.myodbc
Sent: Thursday, March 16, 2006 8:59 PM
Subject: Re: NULL columns


>
>
>
>> When doing an insert using NULL in the insert request,
>> what really is being written in the column?
>> Is the word NULL being written?
>> Is any real space being consumed?
>
> In the cases that you really want to store NULL ;-)
>
>
> No, it's not the word "null".
>
> Of course space is consumed.
>
> How much depends, see, for example:
> http://dev.mysql.com/doc/refman/5.0/en/static-format.html
> or
> http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html
>
> This is for MyISAM, InnoDB behaves differently.

InnoDB's old table format in 4.1 and earlier kept a fixed-length column the 
same length even when the value NULL was stored. That, of course, wasted a 
lot of space. The advantage was less fragmentation in updates.

InnoDB's new table format in 5.0 does not use any space to store a NULL. The 
column itself needs one bit to indicate whether the value is NULL or not.

> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Thread
NULL columnsfbsd_user16 Mar
  • Re: NULL columnsMartijn Tonies16 Mar
Re: NULL columnsHeikki Tuuri16 Mar