Cannot create table in MYSQL, ERROR: 1064-CodePudding

I am trying to create a simple table in mysql but I am getting a syntax error.

Here is the code:

CREATE TABLE survey(
    id INTEGER(15)  NOT NULL,
    `name` UNICODE(65) NOT NULL,
    parentId INTEGER(15)  NOT NULL, 
    createdAt TIMESTAMP(30) NOT NULL, 
    modifiedAt TIMESTAMP (30) NOT NULL, 
    surveyUrl UNICODE(100) NOT NULL,
    PRIMARY KEY(id)
);

I have tried changing the UNICODE to STRING and placing the column names between marks with no luck. Thank you!

CodePudding user response：

The error in the code is that the data type of the id and parentId columns is specified as INTEGER(15), but in MySQL, the correct syntax for the INTEGER data type is INTEGER UNSIGNED. The UNSIGNED keyword specifies that the values in the column should be positive integers.

Here is the correct code with the error fixed:

CREATE TABLE survey(
    id INTEGER UNSIGNED NOT NULL,
    `name` UNICODE(65) NOT NULL,
    parentId INTEGER UNSIGNED NOT NULL, 
    createdAt TIMESTAMP(30) NOT NULL, 
    modifiedAt TIMESTAMP (30) NOT NULL, 
    surveyUrl UNICODE(100) NOT NULL,
    PRIMARY KEY(id)
);

In this code, the id and parentId columns are defined as INTEGER UNSIGNED instead of INTEGER(15), which is the correct syntax for the INTEGER data type in MySQL.

It is also worth noting that the UNICODE data type is not supported in MySQL. You can either use the VARCHAR data type, which allows you to specify the maximum length of the string, or the TEXT data type, which allows you to store strings of any length.

For example, you could use the VARCHAR data type like this:

CREATE TABLE survey(
    id INTEGER UNSIGNED NOT NULL,
    `name` VARCHAR(65) NOT NULL,
    parentId INTEGER UNSIGNED NOT NULL, 
    createdAt TIMESTAMP(30) NOT NULL, 
    modifiedAt TIMESTAMP (30) NOT NULL, 
    surveyUrl VARCHAR(100) NOT NULL,
    PRIMARY KEY(id)
);

Or you could use the TEXT data type like this:

CREATE TABLE survey(
    id INTEGER UNSIGNED NOT NULL,
    `name` TEXT NOT NULL,
    parentId INTEGER UNSIGNED NOT NULL, 
    createdAt TIMESTAMP(30) NOT NULL, 
    modifiedAt TIMESTAMP (30) NOT NULL, 
    surveyUrl TEXT NOT NULL,
    PRIMARY KEY(id)
);

In either case, the UNICODE data type should be replaced with either VARCHAR or TEXT.

CodePudding user response：

a Datatype UNICOde and the prcision 30 for timestampo are not allowed.

You can switch to following format, iff you need another character set and collation besides the default

More about collations

CREATE TABLE survey(
    id BIGINT  NOT NULL,
    `name` varchar(65) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
    parentId BIGINT  NOT NULL, 
    createdAt TIMESTAMP(6) NOT NULL, 
    modifiedAt TIMESTAMP (6) NOT NULL, 
    surveyUrl varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
    PRIMARY KEY(id)
);