Mysql: Allow Null означает
Чтобы понять, что означает «Разрешить Null» , вам нужно понять, что такое NULL , и понять, что он отличается от простой или пустой строки.
NULL — специальное значение в SQL. Он указывает данные, которые не существуют. Это отличается от данных, которые пусты.
Когда вы создаете таблицу в MySQL, она позволяет указать, разрешено ли вашим полям NULL . Если вы укажете «Разрешить Null» , тогда в этих полях можно будет создавать записи с NULL значениями.
Это может быть полезно вам в некоторых случаях. Например, поле «Да/Нет» может быть логическим значением, но если пользователь не указал их предпочтения, вы можете установить значение NULL , чтобы указать это, а не на «да» или «нет».
Во многих случаях разрешение NULL может быть проблематичным. Поля, установленные на NULL , могут создавать неожиданные результаты в запросах, поскольку NULL не соответствует тем же правилам, что и другие значения во всех случаях. например: если вы запрашиваете приведенное выше поле примера WHERE myfield != 1 , вы можете ожидать получить результаты для всех записей, которые не равны 1. Однако поля NULL не будут возвращены; вам нужно написать для них специальный случай.
NULL также используется как значение по умолчанию при выполнении JOIN запросов, в которых некоторые из результатов не имеют записи для присоединения. Неизвестные поля из объединенной таблицы будут установлены на NULL .
В общем случае, если вы не знаете, что делать с «Разрешить Null» , самым безопасным вариантом является его отсутствие для нулевых значений.
Для получения дополнительной информации см. запись в Wikipedia для NULL в SQL.
Mysql: Allow Null meaning
In order to understand what «Allow Null» means, you need to understand what NULL is, and understand that it is different to simply a zero or blank string.
NULL is a special value in SQL. It indicates data that does not exist. This is different to data that is blank.
When you create a table in MySQL, it allows you to specify whether your fields are allowed to be NULL . If you specify «Allow Null», then it will be possible to create records with NULL values in those fields.
This may be useful to you in some cases. For example, a Yes/No field may be a boolean value, but if the user didn’t specify their preference, you may want to set to NULL to indicate this rather than defaulting to yes or no.
In many cases though, allowing NULL can be problematic. Fields set to NULL can produce unexpected results in queries, because NULL does not follow the same rules as other values in all cases. eg: If you query the above example field WHERE myfield != 1 , you may expect to get results for all the records which are not equal to 1. However NULL fields will not be returned; you need to write a special case for them.
NULL is also used as the default value when doing JOIN queries where some of the results do not have a record to join to. The unknown fields from the joined table will all be set to NULL .
In general, if you’re not sure what to do with «Allow Null», the safest option is to set it to no allow nulls.
Should You Use NULL values in Your SQL Database?
Should You Use NULL values in your SQL Database? If you search for NULL standards in Google, the first page shows you answers from both sides of the debate. Using NULL values in your database is a permanent choice. Once you choose to allow them, you need to allow NULLs in all SQL database tables. Relational databases include NULLs by default, but they all let you reject NULLs should you decide to require developers to enter a value. Allowing NULL values has been an ongoing debate among database administrators for years.
What is a NULL Value?
One common misconception about NULL values is that they represent “nothing” or “no value.” NULLs are indeed a value. They take up space on your database hard drive as opposed to “nothing” that indicates there is no value. It’s common for new database developers to think that they are saving hard drive space by adding NULL values, but this isn’t necessarily the case.
NULL values are used to indicate that you could have a value, but you don’t know what that value should be yet. They are placeholders until you finally collect the data needed to fill the table field with a real value.
You should never confuse NULL values for zeros or blank strings. This is another misconception. When you see a NULL value in a table, it means that you don’t know the value yet. When you see a zero value, it means that the total or integer value is supposed to be zero. You know that the value should be zero. Think of NULL as “unknown.”
You can use NULL values for any data type including integers, decimals, strings, or blobs. Even though many database administrators use NULL, they usually demand that NULLs are not used for numeric values. The reason is that NULLs used for numeric values can become confusing when developing code to calculate data.
The Advantages of Using NULL Values
While it seems silly to use NULLs, they actually have a good purpose in relational databases. Every major database vendor on the market allows you to insert NULLs by default including Microsoft SQL Server and MySQL. The databases automatically exclude these values when using internal functions.
For instance, suppose you want to add a list of order totals. You have some orders that haven’t completed, and you choose to use NULL values as a placeholder. The database programmer uses the internal SUM function to add all the totals. The function automatically removes the NULL values and doesn’t count them in the calculation. This is especially important when you use the AVG (average) function with your calculations. The database removes them from the calculation and your average is only the records with values. If you use a zero value, the function would include them in your calculations and skew results.
Programming languages outside of database SQL also have functions that use NULL. For instance, if you program in the Microsoft NET framework or the Linux-based PHP language, you have functions readily available to you that let you evaluate values for NULL and use them to create logical loops and structures. This is common when you have string values that have NULL as a placeholder. When your program sees a NULL value, you know that there is no real value and you can skip the record or display specific text to your users that a value hasn’t been stored yet. The logic you use for NULLs is your decision, but you have readily available functions that make it much easier to identify a placeholder value versus a real value.
If you used zero values, your programs wouldn’t know the difference between a user entering a zero or the database storing zero as a placeholder. This is one reason advocates for NULL database values prefer it.
With programming, having NULLs in your database improves the logic. You don’t need to account for several different possibilities for placeholder values. You just test for NULL and then use your logic to display content to your users. If you had several different placeholders, you’d need to write code for each one.
Another advantage is that databases allow you to automatically enter NULL into a field when no value is available. This means that you don’t need to figure out a value for each placeholder. You just use NULL and every database programmer recognizes it as the default placeholder.
The Disadvantages of NULL
With all of its advantages, NULL also has disadvantages that database administrators use to argue against using NULLs in any table.
The first one is that NULL is considered a variable-length value. This means that it could be a few bytes or several bytes. The database leaves room for extra bytes should the value be larger than what is stored in the field. The result is that your database might take up more hard drive storage space than if you used regular values.
Database administrators also argue that if all values can’t be filled, then a record shouldn’t be created. This argument doesn’t always work for all environments, but the idea is that a record should only be created when all fields have actual values without any placeholders. For instance, you wouldn’t want to allow a bank transaction to happen if you don’t know the amount of the transaction. This standard works in the financial industry, but it doesn’t work well in other industries such as e-commerce or websites that collect user data.
Another disadvantage is in your database programming procedures. While you can use functions that automatically detect NULL values, custom functions must be created to eliminate NULLs. This means that your SQL procedures might be much longer than necessary, and they can be too complex to read. A database administrator will reject code changes if the procedures are too convoluted and messy.
Should You Use NULL Values?
Now that you’ve seen the advantages and disadvantages, you must decide if you’ll allow NULL values. There are several more advantages than disadvantages. Hard drive space is much cheaper than it was a few decades ago, so using more hard drive space isn’t a good argument. Having cleaner code is much better than having complex code, and you can streamline it when you have NULL values as a default.
NULLs are widely recognized by desktop, web, and database programmers. You don’t need to explain the reasons for them like you do with custom placeholders. They are standards in the industry even with the widespread debate.
If you decide to use NULLs, always use them uniformly across all of your database tables. Every table must allow them, and you should never use multiple placeholders in different tables. This can lead to bugs in your applications.
Take a look at DoordaHost
How did we learn so much about Optimizing SQL? By creating our own hosted data solution! DoordaHost allows you to interrogate our data products in situ, this includes information on all UK addresses via DoordaProperty and data on over 12 million organisations held in DoordaBiz. We do all the data processing all you need to do is query and extract!
Product articles SQL Prompt SQL Code Analysis
This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.
Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.
He is a regular contributor to Simple Talk and SQLServerCentral.
- SQL Prompt
- SQL Code Analysis
Why you Should Always Specify Whether a Column Accepts Nulls
Phil Factor explains the factors that determine whether a column will allow null values, if you don’t specify it explicitly in the column definition. If you rely on the default behavior established by your connection settings, you could be in for some nasty surprises.
Guest post
This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.
Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.
He is a regular contributor to Simple Talk and SQLServerCentral.
In SQL, you should, in most circumstances, specify explicitly whether a column should or shouldn’t allow NULL values. It isn’t a good idea to rely on defaults, and assume that, if don’t specify the nullability of a column explicitly, using NULL or NOT NULL , then the column should be nullable. The rules that govern what happens if you leave out that option for a given datatype are quite difficult to explain, and even if you understand them, will your team or successors be as willing to do so?
SQL’s NOT NULL clause is a “logical constraint”, used to ensure that a column never gets a null value assigned to it. Conversely, the NULL clause makes it clear that you want the column to accept null values. If your tables specifications don’t include these clauses, then the presence or otherwise of nulls is determined from the database properties, connection settings, or the default for the datatype. Basically, you don’t always know, and you can easily end up with NOT NULL columns that you expected to allow nulls.
SQL Prompt’s code analysis feature is a SQL syntax checker and includes a best practice rule, BP014, that will warn you (one violation at a time, in each expression) if you fail to specify a column’s nullability when creating or altering a table, or declaring a table variable.
Why care if a column is or isn’t nullable?
The reason you need to specify this is because a relational database is designed to make it efficient to prevent bad data getting in. Constraints are the means of doing this. You must therefore use NOT NULL for all columns that cannot legitimately contain nulls. If you specify that a column is NOT NULL , you are defining a constraint that ensures that that the column can never hold or accept NULL , so you can’t accidentally leave the value out. By allowing null values in a column you also makes aggregation trickier and can make WHERE clauses give unexpected results unless you use functions such as ISNULL() , IFNULL() , and NULLIF() for handling nulls.
Don’t assume that the column will allow nulls if you don’t specify it. This article will explain why you should always specify whether a column that you are defining in any table allows null values. It cannot go too far into the issue of whether NOT NULL constraints are a good thing, but just explains why you need to state your preference.
You might think that if you don’t include the NOT NULL constraint in the column’s definition, then the column will be nullable. No, wrong. It may be nullable, but it may not. It depends on the datatype, the database settings and the settings of the connection. Unless you can memorize all the rules and guarantee the type of connection used for your DDL scripts, it is much simpler just to accept the single rule that you always specify whether your columns are NULL or NOT NULL .
We’ll now look at the various factors that could determine whether a column gets a NOT NULL constraint, if you don’t specify it.
The ANSI_NULL_DEFAULT database setting
If the programmer does not specify the nullability of a column, when creating or altering a table, then the database settings, specifically the ANSI_NULL_DEFAULT option, determine whether that column is NULL or NOT NULL , unless you have a SQL Server connection that overrides it, or other factors, which I’ll explain, override it.
The ANSI_NULL_DEFAULT option is one of the sql_option settings and the syntax is: