MySQL error #1064 on create table with DOUBLE column-CodePudding

I'm struggling to code this table as it's giving me a #1064 error, and this corresponds to a few potential issues. If someone could point out where I've made a mistake that would be great.

Here is an image of the code I've typed, and the error I've received:

The code:

CREATE TABLE `RENRMyLUoX`.`movie`(
    `mID` INT(20) NOT NULL,
    `title` VARCHAR(50) NULL DEFAULT NULL,
    `relYear` YEAR(4) NULL DEFAULT NULL,
    `category` VARCHAR(50) NULL DEFAULT NULL,
    `runTime` INT(20) NOT NULL,
    `studioName` VARCHAR(50) NULL DEFAULT NULL,
    `description` VARCHAR(50) NULL DEFAULT NULL,
    `rating` DOUBLE(20) NULL DEFAULT NULL,
    PRIMARY KEY(`mID`(20))
) ENGINE = InnoDB CHARSET = latin1 COLLATE latin1_bin;

CodePudding user response：

The DOUBLE datatype should be specified as either DOUBLE or DOUBLE(m, d). I don't understand what (20) is supposed to do... you can simply omit it:

rating DOUBLE NULL DEFAULT NULL,

CodePudding user response：

TRY BELOW CODE CREATE TABLE RENRMyLUoX.movie( mID INT(20) NOT NULL, title VARCHAR(50) NULL DEFAULT NULL, relYear YEAR(4) NULL DEFAULT NULL, category VARCHAR(50) NULL DEFAULT NULL, runTime INT(20) NOT NULL, studioName VARCHAR(50) NULL DEFAULT NULL, description VARCHAR(50) NULL DEFAULT NULL, rating DOUBLE(20) NULL DEFAULT NULL)ENGINE = InnoDB CHARSET = latin1 COLLATE latin1_bin;

ALTER TABLE RENRMyLUoX.movie ADD PRIMARY KEY (mID)

ALTER TABLE RENRMyLUoX.movie MODIFY mID int(20) NOT NULL AUTO_INCREMENT; COMMIT;