MySQL null value not inserting

Question

I have a query like

INSERT INTO support_users VALUES("1",,"2","1","4",,,"2017-05-06 20:24:36");

but new MySQL not inserting given error Error Code: 1064

but I changed it to

INSERT INTO support_users VALUES("1","","2","1","4",,,"2017-05-06 20:24:36");

working

but previous MySQL not having such an issue.how to solve that. without changing query

table definition

CREATE TABLE support_users (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `client` int(11) DEFAULT NULL,
  `user` int(11) DEFAULT NULL,
  `ticket` int(11) DEFAULT NULL,
  `scope` int(11) DEFAULT NULL,
  `notify` tinyint(1) DEFAULT NULL,
  `email` tinyint(1) DEFAULT NULL,
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8;

Could you show the table definition? I guess you have not null constrain at the column. Notice that empty string and null are different values in SQL. — Alexey Usharovski
– Alexey Usharovski, Commented Jul 12, 2019 at 11:48
i have more queries.is there way to change it .using variables — srinath madusanka
– srinath madusanka, Commented Jul 12, 2019 at 11:48
Which MySQL versions are you refering to? What is "previous MySQL"? And what are you using now? — Paul Spiegel
– Paul Spiegel, Commented Jul 12, 2019 at 11:48

Mm-Art-In · Accepted Answer · 2019-07-12 12:05:53Z

3

Firstly, respect the Datatype. If its Integer don't use quotes.

The right way to approach your problem will be use of NULL. So this works across any version of MySQL.

INSERT INTO support_users VALUES(1,NULL,2,1,4,NULL,NULL,'2017-05-06 20:24:36');

edited Jul 12, 2019 at 12:05

Mm-Art-In

23k13 gold badges81 silver badges144 bronze badges

answered Jul 12, 2019 at 11:56

Ganesh Chandrasekaran

1,95614 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

1)

Integer column values should not be quoted.

So "1" becomes 1

2)

Auto Increment values should not be manally given to the SQL -- let it work it out itself.

3)

Inserting NULL using the NULL keyword.

4)

Your date-time column is set in its definition to show the current date-time of the row being inserted when it is inserted. As you are inserting (rather than updating, etc.) you therefore do not need to include this data.

5)

It is easier for your sanity to also list which columns are being added to with the INSERT instruction.

6)

Use single quotes ' around string data not double quotes ("), when working directly with MySQL.

Result:

INSERT INTO support_users( `id`, `ticket`, `scope` , `notify`)     
       VALUES (1, 2,1,4);

This will cause errors if you try to insert twice, because you are forcing the Primary Key (id) to be 1. To avoid this, skip the Auto Increment (id) column value.

Or for clarity only; the full SQL:

INSERT INTO support_users VALUES (1, NULL, 2, 1, 4, NULL, NULL, '2017-05-06 20:24:36');

Skipping the Auto Increment (id) column value (as referenced above):

INSERT INTO support_users VALUES (NULL, NULL, 2, 1, 4, NULL, NULL, '2017-05-06 20:24:36');

Collectives™ on Stack Overflow

MySQL null value not inserting

2 Answers 2

Comments

1)

2)

3)

4)

5)

6)

Result:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1)

2)

3)

4)

5)

6)

Result:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related