Why is creating this table generating #1064 error in phpmyadmin?

Question

Im trying to create a table and this error keeps popping:

"#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, url VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_general_ci N' at line 2"

My code:

CREATE TABLE `stema`.`products`(
    `code` DOUBLE(10) NOT NULL,
    `url` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
    `product_name` VARCHAR(100) NULL,
    `generic_name` VARCHAR(100) NULL,
    `quantity` VARCHAR(50) NULL,
    `packaging` VARCHAR(100) NULL,
    `brands` VARCHAR(50) NULL,
    `categories` VARCHAR(100) NULL,
    `countries` VARCHAR(50) NULL,
    `ingredients_text` VARCHAR(200) NULL,
    `allergens` VARCHAR(100) NULL,
    `traces` VARCHAR(100) NULL,
    `serving_size` VARCHAR(100) NULL,
    `serving_quantity` INT(10) NULL,
    `additives_n` INT(10) NULL,
    `additives_tags` VARCHAR(100) NULL,
    `nutriscore_score` INT(10) NULL,
    `nutriscore_grade` VARCHAR(10) NULL,
    `nova_group` VARCHAR(10) NULL,
    `pnns_groups_1` VARCHAR(100) NULL,
    `pnns_groups_2` VARCHAR(100) NULL,
    `main_category` VARCHAR(100) NULL,
    `image_url` VARCHAR(500) NULL,
    `image_small_url` VARCHAR(500) NULL,
    `image_front_url` VARCHAR(500) NULL,
    `energy-kj_100g` DOUBLE(10) NULL,
    `energy-kcal_100g` DOUBLE(10) NULL,
    `energy_100g` DOUBLE(10) NULL,
    `fat_100g` DOUBLE(10) NULL,
    `saturated-fat_100g` DOUBLE(10) NULL,
    `sugars_100g` DOUBLE(10) NULL,
    `salt_100g` DOUBLE(10) NULL,
    `sodium_100g` DOUBLE(10) NULL,
    PRIMARY KEY(`code`)
);

Even if double(10) were allowed, you would never have a floating point number be a primary key on a table. — Gordon Linoff

Bill Karwin Bill Karwin · Accepted Answer · 2020-09-22T02:39:35

Review the documentation for the DOUBLE PRECISION data type:

https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html

MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, (M,D) means than values can be stored with up to M digits in total, of which D digits may be after the decimal point.

It requires two numeric arguments, not one. You have only one.

Also take note:

As of MySQL 8.0.17, the nonstandard FLOAT(M,D) and DOUBLE(M,D) syntax is deprecated and support for it will be removed in a future MySQL version.

To make sure your code remains compatible as you upgrade MySQL, I recommend you get into the habit of declaring the DOUBLE PRECISION without the numeric arguments.

Why is creating this table generating #1064 error in phpmyadmin?

1 Answers