MySQL error 1064 on creating a table in PhpMyAdmin

Question

CREATE TABLE `cuenta` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(100) NOT NULL,
    `institucion` INT(100) NOT NULL,
    `fechaApertura` DATETIME NOT NULL,
    `fechaCierre` DATETIME NOT NULL,
    `moneda` VARCHAR(100) NOT NULL,
    `saldoDisponible` DOUBLE(20) NOT NULL,
    `saldoInicial` DOUBLE(20) NOT NULL,
    `saldoPagado` DOUBLE(20) NOT NULL,
    `saldoRestante` DOUBLE(20) NOT NULL,
    `pago` DOUBLE(20) NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE = INNODB;

I´m trying to create a table with that scrip but I get a error in phpmyadmin

Something is wrong in your syntax near ') NOT NULL, `saldoInicial` DOUBLE(20) NOT NULL, `saldoPagado` DOUBL...' in line 8

I´m new to data bases so help.

Answer 1

You are making a mistake in the lack of precision of the field, try the code below, decimal has a better precision for handling monetary (financial) applications, according to the documentation https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html

CREATE TABLE `cuenta` (
        `id` INT NOT NULL AUTO_INCREMENT,
        `nombre` VARCHAR(100) NOT NULL,
        `institucion` INT(100) NOT NULL,
        `fechaApertura` DATETIME NOT NULL,
        `fechaCierre` DATETIME NOT NULL,
        `moneda` VARCHAR(100) NOT NULL,
        `saldoDisponible` DECIMAL(20,2) NOT NULL,
        `saldoInicial` DECIMAL(20,2) NOT NULL,
        `saldoPagado` DECIMAL(20,2) NOT NULL,
        `saldoRestante` DECIMAL(20,2) NOT NULL,
        `pago` DECIMAL(20,2) NOT NULL,
        PRIMARY KEY(`id`)
    ) ENGINE = INNODB;

Answer 2

You need to make the double type DOUBLE(M,D)

MySQL allows a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here values can be stored up to M digits in total where D represents the decimal point. For example, a column defined as FLOAT(8,5) will look like -999.99999. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

Try this

    CREATE TABLE `cuenta` (
`id` INT NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(100) NOT NULL,
`institucion` INT(100) NOT NULL,
`fechaApertura` DATETIME NOT NULL,
`fechaCierre` DATETIME NOT NULL,
`moneda` VARCHAR(100) NOT NULL,
`saldoDisponible` DOUBLE(20,2) NOT NULL,
`saldoInicial` DOUBLE(20,2) NOT NULL,
`saldoPagado` DOUBLE(20,2) NOT NULL,
`saldoRestante` DOUBLE(20,2) NOT NULL,
`pago` DOUBLE(20,2) NOT NULL,
PRIMARY KEY(`id`)) ENGINE = INNODB;