In MySQL, how can I insert date and time automatically while inserting NULL values to the other columns?

MySQLMySQLi Database

In MySQL, we can insert current date and time automatically to a column on inserting the NULL values in other columns by declaring that column as DEFAULT CURRENT_TIMESTAMP. In this case, we cannot declare the column NOT NULL in which we want to insert NULL values.

mysql> Create Table Testing1(Name Varchar(20),
RegStudent TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.15 sec)

Above query will create a table ‘Testing1’ with a column named ‘Name’(not declared ‘NOT NULL’) and other column named ‘RegDate’ declared as DEFAULT CURRENT_TIMESTAMP. Now, on inserting the NULL values Name column, the current date and time will be inserted in the other column automatically.

mysql> Insert into Testing1(Name) Values(NULL);
Query OK, 1 row affected (0.08 sec)

mysql> Insert into Testing1(Name) Values(NULL);
Query OK, 1 row affected (0.04 sec)

mysql> Select * from Testing1;
+------+---------------------+
| Name | RegStudent |
+------+---------------------+
| NULL | 2017-10-29 04:46:59 |
| NULL | 2017-10-29 04:47:02 |
+------+---------------------+
2 rows in set (0.05 sec)

From the above queries, we can see that on inserting the NULL values in ‘Name’, the date and time are also inserting automatically.

raja
Published on 19-Feb-2018 08:32:14
Advertisements