JIYIK CN >

This article teaches you how to use as 5.6.5in MySQL versions lower than . Thus, you can prevent MySQL error 1293.CURRENT_TIMESTAMPDEFAULT

Our approach involves reordering table columns and using DEFAULT 0and time values.

Reproduce the error in MySQL CURRENT_TIMESTAMPusingDEFAULT

Before I show you how to CURRENT_TIMESTAMPuse as DEFAULT, let's reproduce the error. This is the error you'll get when you try to CURRENT_TIMESTAMPuse as .DEFAULT

To continue:

• Download 5.6.5a version of MySQL below . You'll get it as part of XAMPP 1.8.0 , or some other way.

• Open MySQL in XAMPP and mysql -u root -plog in using . You will notice that the MySQL version is 5.5.2a, so it is smaller than 5.6.5.

• Create a just_a_test_dbdatabase named .

Now, create a table in the database using the following SQL site_users:

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 will receive an error like the one below:

Reorder table columns to use CURRENT_TIMESTAMPas in MySQLDEFAULT

If you reorder table columns in a SQL query, you can use CURRENT_TIMESTAMPas DEFAULT. The column with CURRENT_TIMESTAMPas DEFAULTshould come first; TIMESTAMPother columns with as values ​​should follow.

In the following SQL, we reorder 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 MySQL console:

Use DEFAULT 0in MySQL CURRENT_TIMESTAMPasDEFAULT

Using DEFAULT 0is another option that allows you to use CURRENT_TIMESTAMPas DEFAULT. You apply it to TIMESTAMPa column that does not have a default value.

In our SQL, this is date_validatedthe 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 MySQL console:

Use time values ​​in MySQL using CURRENT_TIMESTAMPasDEFAULT

If the default value of is time, you can use as TIMESTAMPin other columns . This time value is , we used it in the column.CURRENT_TIMESTAMPDEFAULT0000-00-00 00:00:00date_validated

The following SQL shows this:

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 MySQL console: