Jump to content


Check out our Community Blogs

jhonichen

Member Since 11 May 2009
Offline Last Active Dec 13 2009 06:32 AM
-----

Posts I've Made

In Topic: NULL

12 May 2009 - 08:34 PM

ok. to get no mysql strict error
for null column, do not need to define the column on insert and make sure the correct value is inserted, for example, if the data type is int do not insert ''

for not null column set the default value.

In Topic: NULL

12 May 2009 - 08:00 PM

If the field set to null it takes extra 1 bit to define it's null or not (as flag).

Anyway, it's all depends on you, since null and'' has different meanings. we set it to null if "I don't know" is a possible answer. Remember, NULL is NOT the same as zero or empty/blank. Zero means nothing or none, which is a definite value.

for query performance, if we are check null or non null data, nullable field will be faster, if we are using =, <, > like, I think non nullable will be faster.

In Topic: NULL

12 May 2009 - 07:15 PM

For example someone told me if( $field == "" ) will give the same result for a value of null and a value of empty string. However in the links you gave it says operators such as =, <, and > don't work.

Yes, it should returns the same if using PHP, or we can also use if(empty($field)). The operator =, <, > will not work on mysql query but i'll work on PHP code, we need to use IS NULL instead on mysql query.

I guess it just means in the query if you select where field<1 it will not include null ones, but in php null is indeed <1. ?

in php null is treated as empty, I'm not sure if it is treated < 1 or not. you can try to debug it on php.

I'm so very confused what to do now.. I have no idea when I should use null now since you say it will eliminate the rows from indexes. And I am very confused still how to prevent the strict errors. I thought if my original table creation has default of '' it is the same as if it has no default.

Most of the nullable fields will not be used on "WHERE" condition on query. Indexes is only needed when we include the fields on "where" condition in the query. If your fields that you've set to null are not being used on "where" condition and not being treated as indexes, I think it's ok. don't worry about that.

In Topic: NULL

12 May 2009 - 06:44 PM

in mysql, if you set it to 'null' and it'll be consider as word null. if you insist to declare the field, use
insert into test(test_name, description) value('bla bla', NULL)

on php, if you want to set it to null, just don't declare the field.

the different will be around handling the null and '':
MySQL :: MySQL 5.0 Reference Manual :: B.1.5.3 Problems with NULL Values
MySQL :: MySQL 5.0 Reference Manual :: 3.3.4.6 Working with NULL Values

also, null-ing a field, null is not counted on table indexes. Indexes is important for querying speed.

In Topic: NULL

12 May 2009 - 06:15 PM

I've heard from some forums saying that it's better to set the value to NOT NULL because it'll save more space, for varchar/char etc, we better set it to NOT NULL with DEFAULT '' (empty string).

on insert, if we do not have value for that field, we don't need to set the value for it.

for example
create table test (
test_id int(11) NOT NULL AUTO_INCREMENT,
test_name varchar(100) NOT NULL DEFAULT '',
description varchar(500) NOT NULL DEFAULT '',
PRIMARY KEY(test_id)
)
if we do not have value for description,
we can use query
INSERT INTO test (test_name) VALUE ('bla bla')
above query will not bounce error out.

or in php, using Zend Framework for instance:

$dataToInsert = array(
"test_name"=>"bla bla",
)
$db->insert('test', $dataToInsert);

//or below will work too
$dataToInsert = array(
"test_name"=>"bla bla",
"description"=>"",
)
$db->insert('test', $dataToInsert);

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download