Error in Workbench database, birth date, error 0000-00-00

Asked

Viewed 374 times

0

    create table pessoas(
    id int  not null  auto_increment,
    nome varchar(50) not null,
    nascimento date,
    sexo enum('M','F'),
    peso decimal(5,2),
    altura decimal(2,2),
    nacionalidade varchar(20) default 'Brasil',
    primary key (id)
)default charset = utf8;

When I run 

insert into pessoas(id,nome,nascimento,sexo,peso,altura,nacionalidade)
values
(DEFAULT,'JOAO PAULO', 2018-02-25,'M',90,1.68, DEFAULT);

is giving this result:

JOAO PAULO 0000-00-00 M 90.00 0.99 Brazil

You’re showing that error:

1 31 19:37:13 Enter into persons(id,name,birth,sex,weight,height,nationality,)
values (DEFAULT, 'JOAO PAULO', 2018-02-25,’M',90,1.68, DEFAULT)
1 Row(s) affected, 2 Warning(s):
1264 Out of range value adjusted for column 'birth' at Row 1
1264 Out of range value adjusted for column 'height' at Row 1 0.015 sec

Where am I going wrong?

mysql

  • Wouldn’t be the lack of quotation marks on the date of birth?

  • '2018-02-25'....

  • Missing quotes in other values tb.

  • João Paulo, the best way to thank is to vote positive in the useful answers and mark an answer as "accepted"

  • more where you have that option?

2 answers

2

In addition to the quotes that are missing when reporting the date, you are also creating a field (altura) with no more than 2 numbers, being 2 of precision (is after the point).

Mathematically speaking, this is only possible with numbers between 0.01 and 0.99. Therefore it does not suit your case (height in meters). So you should use the structure as follows.

CREATE TABLE pessoas(
  id INT NOT NULL AUTO_INCREMENT,
  nome VARCHAR(50) NOT NULL,
  nascimento DATE,
  sexo ENUM('M', 'F'),
  peso DECIMAL(5, 2),

  /* Permite 3 números, sendo 2 deles de precisão (fica após a vírgula ou ponto) */
  altura DECIMAL(3, 2),

  nacionalidade VARCHAR(20) DEFAULT 'Brasil',
  PRIMARY KEY(id)
) DEFAULT CHARSET = utf8;

Or you can update the table.

ALTER TABLE `pessoas` CHANGE `altura` `altura` DECIMAL(3,2) NULL DEFAULT NULL;

Demonstration

0

Do the Insert like this:

INSERT INTO pessoas (id, nome, nascimento, sexo, peso, altura, nacionalidade) VALUES (DEFAULT, 'JOAO PAULO', '2018-02-25', 'M', 90, 1.68, DEFAULT);

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.