By Pablo Puig
Have you ever been confused about when to use NULL and when to use an empty string in a database table? Well, database developers use NULLs and empty strings interchangeably within their databases. That's not correct because NULL and empty string represent different things in the relational database. Using these values incorrectly or opting for the wrong one can have massively different results.
MySQL relational database is widely used by developers due to its advanced features, including various transaction support and built-in functions. The RDBMS standard library functions are a particular set of routines performing a specific task and returning results quickly. MySQL functions handle complex operations, including string formats, mathematical computations, I/O processing, and more.
This article will help you understand when to use NULL and when to use an empty string in MySQL functions on Alibaba Cloud RDS.
Follow this step-by-step guide to launch an AsparaDB for the RDS instance:
Then, you will be redirected to the Product Purchase Console:
You will be redirected to the order confirmation page:
The service will be activated as soon as the payment is confirmed:
Note: It will take some time to prepare and launch your instance. Once the state of your instance is running, you can manage it from the console.
The concept of NULL and empty string often creates confusion since many people think NULL is the same as a MySQL empty string. However, this is not the case.
An empty string is a string instance of zero length. However, a NULL has no value at all.
We can use a test database and apply the knowledge in the production environment to understand the concept better.
Create a test database named info_db on the server using the syntax below:
mysql>Create database info_db CHARACTER SET utf8 COLLATE utf8_general_ci;
Switch to the database:
mysql > use info_db
Then, create a sample users table:
mysql> create table users (id BIGINT PRIMARY KEY, name VARCHAR (50), phone VARCHAR (50))
Use the example below to understand the concept of NULL and empty string:
mysql>INSERT into users (phone) VALUES (NULL);
mysql> INSERT into users (phones) VALUES (');
Both statements insert value in the phone column. However, the first query insets a NULL value, and the second inserts an empty string. The first one could be interpreted as an unknown number, and the second could be interpreted as the user has no phone number.
NULL works well when a UNIQUE field is set. For example, in this case, a phone number is optional. Since the phone number is a unique field, the user with no phone number will get added. However, it will throw an error of unique constraint if you use an empty string here. So, NULL is better.
An empty string is useful when the data comes from multiple resources. NULL is used when some fields are optional, and the data is unknown.
A string refers to a character's sequence. Sometimes strings can be empty or NULL. The difference is that NULL is used to refer to nothing. However, an empty string is used to point to a unique string with zero length.
Changyi - April 14, 2020
Changyi - September 2, 2021
Apache Flink Community China - June 2, 2022
Alibaba Developer - July 15, 2021
XianYu Tech - September 8, 2020
Alibaba Tech - July 11, 2019
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilitiesLearn More
Fully managed and less trouble database servicesLearn More
An on-demand database hosting service for MySQL, SQL Server and PostgreSQL with automated monitoring, backup and disaster recovery capabilitiesLearn More
AnalyticDB for MySQL is a real-time data warehousing service that can process petabytes of data with high concurrency and low latency.Learn More
More Posts by Alibaba Cloud Community