Mysql syntax Error when creating a table #1064

Question

When I create a table in my localhost and it says

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL, register_date DATETIME NOT NULL, last_login DATETIME NOT NUL' at line 6"

(WampServer v 3.0.6 64bit, Apache 2.4.23 - MySql 5.7.14)

I've already try to change DB engine into InnoDB but the error was still appear.

Here is my Query

CREATE TABLE `inventry`.`users` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(225) NOT NULL,
    `email` VARCHAR(225) NOT NULL,
    `password` VARCHAR(300) NOT NULL,
    `usertype` ENUM(0) NOT NULL,
    `register_date` DATETIME NOT NULL,
    `last_login` DATETIME NOT NULL,
    `notes` VARCHAR(225) NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE = InnoDB;

U P D A T E D******

I want store data in this field. but when i save this it getting error. How can i fix this.?

"An enumeration value must be a quoted string literal," (ENUM). Furthermore: "We strongly recommend that you do not use numbers as enumeration values, because it does not save on storage over the appropriate TINYINT or SMALLINT type, and it is easy to mix up the strings and the underlying number values (which might not be the same) if you quote the ENUM values incorrectly. If you do use a number as an enumeration value, always enclose it in quotation marks. If the quotation marks are omitted, the number is regarded as an index." — showdev
– showdev, Commented Jun 19, 2019 at 5:33

Fahmi · Accepted Answer · 2019-06-19 05:03:52Z

1

Try below -

CREATE TABLE users(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(225) NOT NULL,
email VARCHAR(225) NOT NULL,
password VARCHAR(300) NOT NULL,
usertype ENUM('0') NOT NULL,
register_date DATETIME NOT NULL,
last_login DATETIME NOT NULL,
notes VARCHAR(225) NOT NULL,
primary key (id)
)

answered Jun 19, 2019 at 5:03

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Biegeleisen Over a year ago

If ENUM(0) is really the only error, then you should be voting to close as a typo, and not answering.

Fahmi Over a year ago

execute his statement, you can see what other errors are!!

Tim Biegeleisen Over a year ago

There are no other errors besides the ENUM problem.

frankho kevinho · Accepted Answer · 2019-06-19 05:21:00Z

1

just put, ENUM values, in quotes

CREATE TABLEinventry.users(
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(225) NOT NULL,
    email VARCHAR(225) NOT NULL,
    password VARCHAR(300) NOT NULL,
    usertype ENUM(**'0'**) NOT NULL,
    register_dateDATETIME NOT NULL,
    last_loginDATETIME NOT NULL,
    notesVARCHAR(225) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB;

answered Jun 19, 2019 at 5:21

frankho kevinho

263 bronze badges

Collectives™ on Stack Overflow

Mysql syntax Error when creating a table #1064

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related