What role does the data type play when I insert an empty string into a MySQL column that is declared NOT NULL?-Mysql Tutorial-php.cn

What role does the data type play when I insert an empty string into a MySQL column that is declared NOT NULL?

WBOY

Release： 2023-09-09 22:29:02

forward

1269 people have browsed it

当我将空字符串插入声明为 NOT NULL 的 MySQL 列时，数据类型起什么作用？

When we insert an empty string into a MySQL column declared as NOT NULL, the representation of the empty string in the result set depends on the data type. We know that when inserting an empty string, we provide MySQL with the value represented as an integer as INT 0.

Now, if the column has the INTEGER data type, MySQL will display 0 in the result set as shown below. The empty string is mapped to the integer zero.

Example

mysql> create table test(id int NOT NULL, Name Varchar(10));
Query OK, 0 rows affected (0.19 sec)

mysql> Insert into test(id, name) values(&#39;1&#39;, &#39;Gaurav&#39;),(&#39;0&#39;,&#39;Rahul&#39;),(&#39;&#39;,&#39;Aarav&#39;);
Query OK, 3 rows affected, 1 warning (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 1

mysql> Select * from test;
+----+--------+
| id | Name   |
+----+--------+
|  1 | Gaurav |
|  0 | Rahul  |
|  0 | Aarav  |
+----+--------+
3 rows in set (0.00 sec)

Copy after login

But if the column has any other data type (such as VARCHAR), then MySQL will display an empty string in the result set.

mysql> create table test123(id Varchar(10) NOT NULL, Name Varchar(10));
Query OK, 0 rows affected (0.19 sec)

mysql> Insert into test123(id, name) values(&#39;1&#39;, &#39;Gaurav&#39;),(&#39;0&#39;,&#39;Rahul&#39;),(&#39;&#39;,&#39;Aarav&#39;);
Query OK, 3 rows affected, 1 warning (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 1

mysql> Select * from test123;
+----+--------+
| id | Name   |
+----+--------+
|  1 | Gaurav |
|  0 | Rahul  |
|    | Aarav  |
+----+--------+
3 rows in set (0.00 sec)

Copy after login

From the above example, we can see what role the data type plays when we insert an empty string into a MySQL column declared as NOT NULL.

The above is the detailed content of What role does the data type play when I insert an empty string into a MySQL column that is declared NOT NULL?. For more information, please follow other related articles on the PHP Chinese website!