The best data type to store 0, 1, null values in MySQL?

You need to use tinyint(1) unsigned NULL to store the value 0,1 and null values. The syntax is as follows −

yourColumnName TINYINT(1) UNSIGNED NULL;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table StoreValue0and1orNULLDemo
   -> (
   -> isDigit TINYINT(1) UNSIGNED NULL
   -> );
Query OK, 0 rows affected (0.63 sec)

Now you can insert records 0,1, and NULL in the table using insert command. The query is as follows −

mysql> insert into StoreValue0and1orNULLDemo values(0);
Query OK, 1 row affected (0.18 sec)
mysql> insert into StoreValue0and1orNULLDemo values(1);
Query OK, 1 row affected (0.18 sec)
mysql> insert into StoreValue0and1orNULLDemo values(NULL);
Query OK, 1 row affected (0.11 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> select *from StoreValue0and1orNULLDemo;

The following is the output −

+---------+
| isDigit |
+---------+
|       0 |
|       1 |
|    NULL |
+---------+
3 rows in set (0.00 sec)

Karthikeya Boyini

Published on 27-Feb-2019 08:33:27

• Related Questions & Answers
• What is the best data type to store money values in MySQL?
• Display 1 for NULL values in MySQL
• Best data type for storing currency values in a MySQL database?
• Convert MySQL null to 0?
• Typecasting NULL to 0 in MySQL
• Best data type for storing large strings in MySQL?
• Best way to store weekly event in MySQL?
• Which MySQL Data Type can be used to store Negative Number?
• MySQL TINYINT type to return <>1 or IS NULL records
• Replace 0 with null in MySQL?
• Set conditions for columns with values 0 or 1 in MySQL
• How MySQL use YEAR data type to store year value in a table?
• Set the NULL values to 0 and display the entire column in a new column with MySQL SELECT
• BOOLEAN or TINYINT to store values in MySQL?
• How to count null values in MySQL?