MySQL, Better to Insert Null or Empty String

MySQL, better to insert NULL or empty string?

By using NULL you can distinguish between "put no data" and "put empty data".

Some more differences:

• A LENGTH of NULL is NULL, a LENGTH of an empty string is 0.

• NULLs are sorted before the empty strings.

• COUNT(message) will count empty strings but not NULLs

• You can search for an empty string using a bound variable but not for a NULL. This query:

  SELECT  *
  FROM    mytable 
  WHERE   mytext = ?
  

  will never match a NULL in mytext, whatever value you pass from the client. To match NULLs, you'll have to use other query:

  SELECT  *
  FROM    mytable 
  WHERE   mytext IS NULL
  

NULL or empty string more efficient/natural?

In MyISAM MYSQL you save one bit per row not using NULL. As it is stated here:

Declaring columns NULL can reduce the maximum number of columns permitted. For MyISAM tables, NULL columns require additional space in the row to record whether their values are NULL. Each NULL column takes one bit extra, rounded up to the nearest byte.

Take a look here as well:

In addition, while a NULL itself does not require any storage space, NDBCLUSTER reserves 4 bytes per row if the table definition contains any columns defined as NULL, up to 32 NULL columns. (If a MySQL Cluster table is defined with more than 32 NULL columns up to 64 NULL columns, then 8 bytes per row is reserved.)

Moreover it also makes the database work faster at it stated here (taken from stackoverflow - @DavidWinterbottom link didn't work for me, I added a different sourse)

It's harder for MySQL to optimize queries that refer to nullable coumns, because they make indexes, index statistics, and value comparisons more complicated. A nullable column uses more storage space and requires special processing inside MySQL. When a nullable column is indexed, it requires an extra byte per entry and can even cause a fixed-size inded (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM.

In most of the cases non-NULL values behave more predictable when combined with COUNT() and other aggregating function but you can also see a NULL behave according to your needs.

As it is stated here, not all group (aggregate) functions ignore NULL for instance, COUNT() would give you different result that COUNT(*) for a column containing NULL values.

On the other hand as other point out NULL better reflects the meaning of entry - it is an unknown value and if you wanted to count all the hosts you would probably COUNT() to behave exactly as it does.

Is better use an empty value as a '' or as NULL?

The usual contract is:

• NULL means "no information available".
• '' means "there is information available. It's just empty."

Beyond this point there is much philosophical discussion since the invention of NULL in any language, not just SQL.

The only technical point here is: In PostgreSQL NULL can be stored more efficiently than a string of length zero. If that really matters in your case ... we cannot know.

MySQL and PHP - insert NULL rather than empty string

To pass a NULL to MySQL, you do just that.

INSERT INTO table (field,field2) VALUES (NULL,3)

So, in your code, check if $intLat, $intLng are empty, if they are, use NULL instead of '$intLat' or '$intLng'.

$intLat = !empty($intLat) ? "'$intLat'" : "NULL";
$intLng = !empty($intLng) ? "'$intLng'" : "NULL";

$query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng)
          VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$long', 
                  $intLat, $intLng)";

Is leaving a field empty better or filling it with null?

Consider the following table:

create table test1 (
  id int not null,
  first_name varchar(50), -- nullable
  last_name varchar(50)   -- also nullable
);

If first_name is not provided in your UI, you can choose to not insert data into that field by doing:

insert into test1 (id, last_name) values (123, 'Smith');

Or, you can choose to explicitly provide NULL for first_name like so:

insert into test1 (id, first_name, last_name) values (123, NULL, 'Smith');

-- you could also do like this below:
-- insert into test1 values (123, NULL, 'Smith');
-- I just like providing explicit fieldnames and values

Either way you choose, just stay consistent throughout your application. Your results will look the same:

+-----+------------+-----------+
| id  | first_name | last_name |
+-----+------------+-----------+
| 123 | NULL       | Smith     |
| 123 | NULL       | Smith     |
+-----+------------+-----------+

So - to answer the real question: don't define an explicit null in your table creation.

When supplying '' or NULL, just make sure you are consistent. If some first_name are '' and some are NULL, your select statement would have to be:

select * from test1 where first_name is NULL or first_name is '';

That brings another point - what if user typed ' ' (4 spaces)? You would have to ensure that first_name meets certain criteria and trimmed version of first_name goes through validation before being entered in the database. If your database ends up with '', ' ', ' ' etc. you would have to constantly run:

select * from test1 where first_name is NULL or trim(first_name) = '';
--or--
--select * from test1 where first_name is NULL or length(trim(first_name)) = 0;

Consistency with NULL first_name will help querying with confidence.

Should I use NULL or an empty string to represent no data in table column?

I strongly disagree with everyone who says to unconditionally use NULL. Allowing a column to be NULL introduces an additional state that you wouldn't have if you set the column up as NOT NULL. Do not do this if you don't need the additional state. That is, if you can't come up with a difference between the meaning of empty string and the meaning of null, then set the column up as NOT NULL and use empty string to represent empty. Representing the same thing in two different ways is a bad idea.

Most of the people who told you to use NULL also gave an example where NULL would mean something different than empty string. And in those examples, they are right.

Most of the time, however, NULL is a needless extra state that just forces programmers to have to handle more cases. As others have mentioned, Oracle does not allow this extra state to exist because it treats NULL and empty string as the same thing (it is impossible to store an empty string in a column that does not allow null in Oracle).

Difference between NULL and Blank Value in Mysql

1. NULL is an absence of a value. An empty string is a value, but is just empty. NULL is special to a database.

2. NULL has no bounds, it can be used for string, integer, date, etc. fields in a database.

3. NULL isn't allocated any memory, the string with NULL value is just a pointer which is pointing to nowhere in memory. however, Empty IS allocated to a memory location, although the value stored in the memory is "".

Golang Insert NULL into sql instead of empty string

In my code I have a function that converts a string to sql.NullString

func NewNullString(s string) sql.NullString {
    if len(s) == 0 {
        return sql.NullString{}
    }
    return sql.NullString{
         String: s,
         Valid: true,
    }
}

Then whenever I am using Exec I wrap my strings that could be NULL in the DB with the NewNullString function.

db.Exec(`
  insert into
      users first_name, last_name, email
      values (?,?,?)`,
  firstName,
  lastName,
  NewNullString(email),
)

---

Related Topics