php mysql not null
MySQL NULL or NOT NULL That is The Question?
What is the difference between NULL and NOT NULL? And when should they be used?
8 Answers 8
NULL means you do not have to provide a value for the field.
NOT NULL means you must provide a value for the fields.
For example, if you are building a table of registered users for a system, you might want to make sure the user-id is always populated with a value (i.e. NOT NULL), but the optional spouses name field, can be left empty (NULL)
Having fields which don’t have a meaningful meaning for NULL nullable is likely to introduce bugs, when nulls enter them by accident. Using NOT NULL prevents this.
The commonest reason for NULL fields is that you have a foreign key field which is options, i.e. not always linked, for a «zero or one» relationship.
If you find you have a table with lots of columns many of which can be NULL, that starts sounding like an antipattern, consider whether vertical partitioning makes more sense in your application context 🙂
What is the difference between NULL and NOT NULL?
And when should they be used?
That is determined by your business rules.
Generally you want as many columns as possible to be NOT NULL because you want to be sure data is always there.
Fields that aren’t NOT NULL can potentially have their value as NULL (which essentially means a missing/unknown/unspecified value). NULL behaves differently than normal values, see here for more info.
NOT NULL is a column constraint and should be used when you have a column that’s not in a primary key (primary keys are intrinsically not-null so it’s silly to say NOT NULL explicitly about them) of which you know the values will always be known (not unknown or missing) so there’s no need for nulls in that column.
As others have answered, NOT NULL simply means that NULL is not a permitted value. However, you will always have the option of empty string » (for varchar ) or 0 (for int ), etc.
The main hiccup with allowing NULL columns is that they will never be found with the <> (not equal) operator. For example, with the following category s
The = operator works as expected
However, the <> operator will exclude any NULL entries.
In some cases, you may want the safeguard for most of the rows, but there are exceptions.
If you decide to allow NULL values, consider writing your <> statements with an additional condition to detect NULL s.
Also note that NULL is not equal to anything else, even not to NULL itself.
Here is an example of how you can see if something is NULL:
If you’re not sure, use NOT NULL.
- Despite the common belief, NOT NULL doesn’t require you to fill all fields; it just means whatever you omit will have the default value. So no, it doesn’t mean pain. Also, NULL is less efficient in terms of indexing, and causes many edge case situations when processing what you receive from a query.
So, while of course NULL values have a theoretical meaning (and in rare cases you can benefit from this), most of the time NOT NULL is the way to go. NOT NULL makes your fields work like any variable: they always have a value, and you decide if that value means something or not. And yes, if you need all the possible values and one extra value that tells you there’s simply nothing there, you can still use NULL.
So why do they love NULL so much?
The whole phenomenon reminds me of the good old isset debate where people are somehow obsessed with the beauty of looking at a nonexistent array index and getting an error message. This is completely pointless. Practical defaults are a blessing, they simplify the work in a «you know what I mean» style and you can write more readable, more concise, more expressive code that will make sense even after you spend 4 years with other projects.
Inevitable NULLs
- If you have JOINs, you will encounter NULLs sooner or later, when you want to join another row but it’s not there. This is the only valid case where you can’t really live without NULLs; you must know it’s not just an empty record you got, it’s no record at all.
Otherwise? NOT NULL is convenient, efficient, and gives you fewer surprises. In return, it will be called ignorant and outrageous by some people with semantical-compulsive disorder. (Which is not a thing but should be.)
NULL поля в MySQL
Вступление
Часто на форумах и даже в учебниках пишут о том, что лучше не использовать NULL поля в MySQL. В этих утверждениях смущает тот факт, что никто не удосуживается объяснить, почему NULL – это зло. Эта заметка призвана разобраться, что такое NULL в MySQL и так ли страшен чёрт, как его малюют.
Что такое NULL?
Наряду с множеством типов данных в БД, NULL стоит особняком. NULL означает отсутствие значения.
Зачем использовать особый тип данных для того, чтоб указать отсутствие значения, когда можно просто вставить пустую строку, например? Этот вопрос мне всегда казался глупым, и я удивляюсь тому, как в книгах и статьях уделяют ему достаточное количество внимания.
Чем опасен NULL?
Сравнение NULL с любым другим значением, даже с родственным (в большинстве языков программирования, в частности, в PHP null, 0, false это одно и то же, если не применять строгого сравнения, которое включает сравнение типов) ему FALSE вернёт NULL. Отсюда следует первая ловушка.
Допустим, у нас есть таблица:
Добавим в неё 2 записи:
INSERT INTO users (name, family) VALUES(‘Андрей’, ‘Романов’), (‘Иван’, NULL);
В случае, если вы захотите объединить имя и фамилию, получив ФИО одним полем, например, таким запросом:
SELECT CONCAT(name, ‘ ‘, family) FROM `users`
MySQL не оправдает ваших ожиданий. Вы получите NULL вместо Ивана.
Немного неожиданно, правда?
На деле же ничего неожиданного нет, если вы помните, что любая операция с NULL вернёт NULL, кроме специальных операций, предназначенных для работы с NULL: IS NULL, IS NOT NULL, IFNULL()
Сортировка по NULL
Всего лишь хочу опровергнуть некоторые фразы из русского мануала MySQL о том, что при сортировке по столбцу, содержащим NULL значения, эти самые NULL значиния всега оказываются наверху. Это не так.
SELECT name, family FROM `users` ORDER BY family ASC
SELECT name, family FROM `users` ORDER BY family DESC
Как видим, NULL считается наименьшим значением, и порядок сортировки на него действует.
Группировки и NULL
Все просто.
MySQL группирует по NULL так же как и по любому другому полю.
Добавим нашей таблице users столбец score INT UNSIGNED NULL;
Подсчитаем сколько всего пользователей набрали то или иное количество очков, т.е. сгруппируем выборку по полю score
SELECT COUNT(*), score FROM `users` GROUP BY score
Как видно, MySQL сгруппировала 2 строки с score = NULL
Индексы и NULL
Откуда-то ходит заблуждение о том, что MySQL не использует индексы, если столбец может принимать значения NULL.
Это не так!
Проведём несколько экспериментов.
Выберем все записи, где score = NULL. Не забываем, что мы для этого должны использовать конструкцию IS NULL
EXPLAIN SELECT * FROM `users` WHERE score IS NULL
Индекс используется.
Выберем все записи, где количество очков больше, например, пяти.
EXPLAIN SELECT * FROM `users` WHERE `score` > 5
Индекс используется.
Найдем пользователя, у которого ровно 7 очков
EXPLAIN SELECT * FROM `users` WHERE `score` = 7
Индекс используется.
Можно сделать вывод: заблуждение действительно таковым и оказалось.
Можно смело использовать NULL-поля при создании индекса и индекс будет работать.
Когда следует использовать NULL?
Ранее я привел пример таблицы сайтов, которая содержит поле PR.
PR – это целочисленное значение, которое может принимать значение 0, к тому же оно может быть в состоянии «не посчитано». Как реализовать хранение такого свойства в таблице?
Зачем отказываться от того, что язык предлагает тебе «из коробки»?
Значение NULL для такого поля подходит идеально. NULL – означает «нет значения», т.е. оно ещё не посчитано. Если значение равно нулю, значит оно действительно равно нулю. Все просто.
Второй пример подходящего случая для использования NULL – это поле-потомок.
Поле-потомок указывает на id записи из другой (или же этой же таблицы). Примером такого поля может быть parent_id.
Выводы
Нет причин бояться создания NULL полей. Надо хорошо понимать, что NULL в MySQL это не ноль и не false – это отсутствие значения. Надо знать, как MySQL работает с NULL: нюансы есть, но их не много. У MySQL нет проблем с индексацией NULL полей.
Does an enum in MySQL need to be NOT NULL?
Lets say I have this:
Is putting a NOT NULL on the end necessary as it can only be Y and N?
EDT: based on comments, if I know the software always sets it to ‘N’ or ‘Y’ and is hardcoded in then is it OK to leave it off or could it still potentially become null some how.
5 Answers 5
MySQL will allow the value to be NULL if you do not specify NOT NULL in the column definition.
Here’s a quick test:
So MySQL does respect the default value, but also allows NULLs. (Interestingly, it will truncate invalid values and allow blank strings as well, but that’s a different issue)
To set a default value ‘N’ for the column with a NOT NULL constraint, do this:
Explanation: According to the MySQL reference manual:
If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted
In ENUM data type, if you do not declare NOT NULL then it gives the default value NULL. However, if you declare NOT NULL then it gives the first value from the ENUM.
Answer from Ian Clelland is very good but more details :
Nope, not necessary at all, it’ll just default it to ‘N’ as you’re probably expecting.
Edit: Commenter made me go off and test this, you should add not null unless you want null to be a valid value. Having default ‘N’ will default it to N if you leave the column out of your insert SQL, but if you set the value to null in the insert or update it will insert a row with null for the value, as you might not want.
MySQL NOT NULL Constraint
Summary: in this tutorial, you will learn how to define a NOT NULL constraint for a column, add a NOT NULL constraint to an existing column, and remove a NOT NULL constraint from a column.
Introduction to the MySQL NOT NULL constraint
The syntax of defining a NOT NULL constraint is as follows:
A column may contain only one NOT NULL constraint which specifies a rule that the column must not contain any NULL value. In other words, if you update or insert NULL into a NOT NULL column, MySQL will issue an error.
The following CREATE TABLE statement creates the tasks table:
In the tasks table, we explicitly define the title and start_date columns with NOT NULL constraints. The id column has the PRIMARY KEY constraint, therefore, it implicitly includes a NOT NULL constraint.
The end_date column can have NULL values, assuming that when you create a new task, you may not know when the task can be completed.
It’s a good practice to have the NOT NULL constraint in every column of a table unless you have a good reason not to do so.
Add a NOT NULL constraint to an existing column
Typically, you add NOT NULL constraints to columns when you create the table. Sometimes, you want to add a NOT NULL constraint to a NULL-able column of an existing table. In this case, you use the following steps:
Consider the following example.
The following statement inserts some rows into the tasks table for the demonstration.
Suppose that you want to force users to give an estimated end date when creating a new task. To implement this rule, you add a NOT NULL constraint to the end_date column of the tasks table.
First, use the IS NULL operator to find rows with NULLs in the column end_date :
This query verifies the update:
Third, add a NOT NULL constraint to the end_date column using the following ALTER TABLE statement:
In this case, the name of the old and new column names are the same except that the column must have a NOT NULL constraint:
Let’s verify the change by using the DESCRIBE statement:
As you see, the NOT NULL constraint was added to the end_date column successfully.
Drop a NOT NULL constraint
To drop a NOT NULL constraint for a column, you use the ALTER TABLE..MODIFY statement:
Note that the column definition (column_definition) must restate the original column definition without the NOT NULL constraint.
For example, the following statement removes the NOT NULL constraint from the end_date column in the tasks table:
To ensure that the statement actually removed the NOT NULL constraint, you can use the SHOW CREATE TABLE command to view the full column definition:
In this tutorial, you have learned how to define a NOT NULL constraint for a column, add a NOT NULL constraint to a column, and remove a NOT NULL constraint from a column.
MySQL ignores the NOT NULL constraint
I have created a table with NOT NULL constraints on some columns in MySQL. Then in PHP I wrote a script to insert data, with an insert query. When I omit one of the NOT NULL columns in this insert statement I would expect an error message from MySQL, and I would expect my script to fail. Instead, MySQL inserts empty strings in the NOT NULL fields. In other omitted fields the data is NULL, which is fine. Could someone tell me what I did wrong here?
I’m using this table:
And this insert statement:
Or using bind variables:
3 Answers 3
If you’re sure you’re not using explicit default values, then check your strict mode:
As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:
If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.
If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:
If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.
If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.