Error making an insert with an empty field mysql

Question

Error making an insert with an empty field mysql

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have a problem with inserting data into a table.

The case is that when doing the insertion I do not take the field '' as a value null or 0 I just skip error. I have already checked the table and the field that in particular does not have any restriction of primary key, non-zero value, etc.

I enclose the query:

insert into usuarios.comida(codigo, usuario, codbinsa, fecha, invitado, pedido, hora)
values ('1cb1e6c756', 0300, '0300', '2018-02-05', '', 'Paella Habas Alcachofa. MERLUZA PLANCHA. Couland. |', '0950');

The problem according to the error, from what I have understood, is that it does not convert the '' into a value INT or NULL but considers it as the string empty string.

I await your help.

mysql sql

asked by jose climent penades 05.02.2018 в 12:54

source

2 answers

How do I fix the error "Your PHP installation appears to be missing the MySQL extension which is required by WordPress."? Error creating a variable outside the Script. with jade / pug

score 1 · Answer 1

I was not quite sure about it and I have trasteado a little on the subject coming to the following conclusions:

-I tried to enter strings using the% commated% commas in columns that expected a ' ' . When inserting a null value as in your case int ; the insert was done but an exception was thrown:

Even so, when looking at the table, the value 0 was automatically entered.

When entering a number between quotation marks '' the number entered correctly in the table despite indicating that it is a '2' .

Explanation: By default the SQL format would not accept it, however there are exceptions such as MySQL, Oracle, etc. that accept this type of syntax. When you put the quotes, the value entered inside is interpreted as a string and in particular cases like the ones mentioned, before the query is executed MySQL automatically tries to change the format of String to String , the problem with this is that it can give several results:

Runs correctly
Error and the query is not executed
The number is interpreted and the result is different from the one entered (it changes to other numbers, the quotes move, the value changes, etc)

Therefore, it is not very advisable to use this method not only for the interpretation, but also because, in a minimal way, it increases the query load simply by putting 2 quotes.

Recommendation

SQL is prepared to react to each type of data in a different way. It is never recommended to put quotation marks around numbers in the same way that you do not put numbers in places where you expect a Int or a String , each type of data has its characteristics and is interpreted differently to optimize operation.

The query can be executed in the following ways:

Putting a number 0 directly in the value:

insert into usuarios.comida(codigo, usuario, codbinsa, fecha, invitado, pedido, hora)
values ('1cb1e6c756', 0300, '0300', '2018-02-05', 0, 'Paella Habas Alcachofa. MERLUZA PLANCHA. Couland. |', '0950');

Setting a value Boolean :

insert into usuarios.comida(codigo, usuario, codbinsa, fecha, invitado, pedido, hora)
values ('1cb1e6c756', 0300, '0300', '2018-02-05', NULL, 'Paella Habas Alcachofa. MERLUZA PLANCHA. Couland. |', '0950');

Or simply not indicating that you are going to enter this value and let it default value (my recommendation)

insert into usuarios.comida(codigo, usuario, codbinsa, fecha, pedido, hora)
values ('1cb1e6c756', 0300, '0300', '2018-02-05', 'Paella Habas Alcachofa. MERLUZA PLANCHA. Couland. |', '0950');

Sources: link1 link2

score 0 · Answer 2

To save you the value as NULL you have to specify it directly, without quotes.

insert into usuarios.comida(codigo,usuario,codbinsa,fecha,invitado,pedido,hora) values ('1cb1e6c756',0300,'0300','2018-02-05', NULL,'Paella Habas Alcachofa. MERLUZA PLANCHA. Couland. |','0950');

I leave you more information: here