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, Jul 12 '19 at 11:48
i have more queries.is there way to change it .using variables — srinath madusanka, Jul 12 '19 at 11:48
Which MySQL versions are you refering to? What is "previous MySQL"? And what are you using now? — Paul Spiegel, Jul 12 '19 at 11:48
**Your Solution is here-** [Error #1064 means that MySQL can't understand your command.](https://stackoverflow.com/questions/23515347/how-can-i-fix-mysql-error-1064) — Rajendra Singh, Jul 12 '19 at 11:53
Your query [doesn't work for me on MySQL 5.6](https://www.db-fiddle.com/f/d9Ptb6vrCAbKD9VA32WWSF/0). — Paul Spiegel, Jul 12 '19 at 11:54

score 3 · Answer 1 · edited Jul 12 '19 at 12:05

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 '19 at 12:05

Martin

22,212
11
70
132

answered Jul 12 '19 at 11:56

Ganesh Chandrasekaran

1,578
12
17

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

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');

MySQL null value not inserting

2 Answers2

1)

2)

3)

4)

5)

6)

Result: