How to Use CURRENT_TIMESTAMP as Default in MySQL
-
Reproduce the Error When Using
CURRENT_TIMESTAMP
asDEFAULT
in MySQL -
Reorder the Table Columns to Use
CURRENT_TIMESTAMP
asDEFAULT
in MySQL -
Use
DEFAULT 0
to UseCURRENT_TIMESTAMP
asDEFAULT
in MySQL -
Use a Time Value to Use
CURRENT_TIMESTAMP
asDEFAULT
in MySQL
This article teaches you how to use CURRENT_TIMESTAMP
as DEFAULT
in the MySQL versions less than 5.6.5
. As a result, you can prevent MySQL Error 1293.
Our methods include reordering the table columns and using DEFAULT 0
and a time value.
Reproduce the Error When Using CURRENT_TIMESTAMP
as DEFAULT
in MySQL
Let’s reproduce the error before showing you how to use CURRENT_TIMESTAMP
as DEFAULT
. This is the error you’ll get when you try to use CURRENT_TIMESTAMP
as DEFAULT
.
To follow along, do the following:
-
Download the MySQL version less than
5.6.5
. You’ll get this as part of XAMPP 1.8.0, or other means. -
Open MySQL in XAMPP, log in with
mysql -u root -p
. You’ll notice the MySQL version is5.5.2a
, so it’s less than5.6.5
. -
Create a database called
just_a_test_db
.
Now, use the following SQL to create a site_users
table in the database:
CREATE TABLE site_users (
user_id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(255) NOT NULL UNIQUE,
user_firstname VARCHAR(255) NOT NULL,
user_surname VARCHAR(255) NOT NULL,
user_email_address VARCHAR(255) NOT NULL UNIQUE,
user_password CHAR(40) NOT NULL,
is_active BOOL NOT NULL DEFAULT FALSE,
is_validated BOOL NOT NULL DEFAULT FALSE,
date_validated TIMESTAMP,
date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id)
) Engine=InnoDB;
You’ll get an error as seen in the following image:
Reorder the Table Columns to Use CURRENT_TIMESTAMP
as DEFAULT
in MySQL
You can use CURRENT_TIMESTAMP
as DEFAULT
if you reorder the table columns in your SQL query. The column with a CURRENT_TIMESTAMP
as DEFAULT
should come first; other columns with TIMESTAMP
values should follow.
In the following SQL, we’ve reordered the SQL, and MySQL creates the table:
CREATE TABLE site_users (
user_id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(255) NOT NULL UNIQUE,
user_firstname VARCHAR(255) NOT NULL,
user_surname VARCHAR(255) NOT NULL,
user_email_address VARCHAR(255) NOT NULL UNIQUE,
user_password CHAR(40) NOT NULL,
is_active BOOL NOT NULL DEFAULT FALSE,
is_validated BOOL NOT NULL DEFAULT FALSE,
date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
date_validated TIMESTAMP,
PRIMARY KEY (user_id)
) Engine=InnoDB;
Output in the MySQL console:
Use DEFAULT 0
to Use CURRENT_TIMESTAMP
as DEFAULT
in MySQL
Using DEFAULT 0
is another option that’ll allow you to use CURRENT_TIMESTAMP
as DEFAULT
. You’ll apply it to TIMESTAMP
columns that do not have a default value.
From our SQL, this is the date_validated
column. The following SQL shows you how to do this:
CREATE TABLE site_users_2 (
user_id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(255) NOT NULL UNIQUE,
user_firstname VARCHAR(255) NOT NULL,
user_surname VARCHAR(255) NOT NULL,
user_email_address VARCHAR(255) NOT NULL UNIQUE,
user_password CHAR(40) NOT NULL,
is_active BOOL NOT NULL DEFAULT FALSE,
is_validated BOOL NOT NULL DEFAULT FALSE,
date_validated TIMESTAMP NOT NULL DEFAULT 0,
date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id)
) Engine=InnoDB;
Output in the MySQL console:
Use a Time Value to Use CURRENT_TIMESTAMP
as DEFAULT
in MySQL
If the default of TIMESTAMP
is a time, you can use CURRENT_TIMESTAMP
as DEFAULT
on other columns. This time value is 0000-00-00 00:00:00
, and we’ve used it on the date_validated
column.
The following SQL shows this in action:
CREATE TABLE site_users_3 (
user_id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(255) NOT NULL UNIQUE,
user_firstname VARCHAR(255) NOT NULL,
user_surname VARCHAR(255) NOT NULL,
user_email_address VARCHAR(255) NOT NULL UNIQUE,
user_password CHAR(40) NOT NULL,
is_active BOOL NOT NULL DEFAULT FALSE,
is_validated BOOL NOT NULL DEFAULT FALSE,
date_validated TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id)
) Engine=InnoDB;
Output in the MySQL console:
Habdul Hazeez is a technical writer with amazing research skills. He can connect the dots, and make sense of data that are scattered across different media.
LinkedIn