Does MySQL Insert allow not specifying all fields?

Question

I have two mysql insert statements. The one with all the fields specified in insert statement works fine and insert record to testTable.(Even when http_referer is empty the insert statement insert records to table with referer field empty)

First Insert statement with all fields specified:

mysql_query("INSERT INTO testTable VALUES('$ID','".$_SERVER['REMOTE_ADDR']."',NOW(),'Page1','".$_SERVER['HTTP_REFERER']."')");

The problem is with second insert statement that doesn't insert any record to testTable! Could you guys tell me why my second insert statement doesn't insert any record to testTable?

Second insert Statment:

mysql_query("INSERT INTO testTable VALUES('$ID','".$_SERVER['REMOTE_ADDR']."',NOW(),'Page1')");

Create Table:

CREATE TABLE IF NOT EXISTS `testTable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(32) DEFAULT NULL,
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Title` varchar(32) NOT NULL,
  `Ref` varchar(250) NULL default '',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1784 ;

For anyone hitting this question because they're wondering whether `insert testTable () values ();` is valid... it is (so long as all fields have a default): http://sqlfiddle.com/#!9/163e74/1 — JohnLBevan, Jan 07 '22 at 14:22

score 6 · Answer 1 · edited May 25 '19 at 14:37

6

Yes, by using a column list.

$sql = "INSERT INTO table (`ip`, `date`, `Title`) VALUES ('".$_SERVER['REMOTE_ADDR']."', NOW(), 'Page 1')";

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which.

edited May 25 '19 at 14:37

Zoe

27,060
21
118
148

answered Feb 08 '13 at 19:49

Kermit

33,827
13
85
121

Thanks for your reply. So you mean i need to specify the column names that i want to insert when trying to omit any column and when trying to insert all columns then i don't need to specify column names in insert statement ? – user1788736 Feb 08 '13 at 19:53
1

@user1788736 Correct. The columns that have a default value (`NULL`, `AUTO INCREMENT` or otherwise) can be omitted from the column list. – Kermit Feb 08 '13 at 19:54
Thanks all. I forgot that i need to specify fields when omitting any fields in insert statement. Now i know that i need to specify the columns i want in insert statement when omitting any column! – user1788736 Feb 08 '13 at 20:04
@user1788736 Unless your table structure will **never** change, you should *always* specify a column list. – Kermit Feb 08 '13 at 20:04

score 5 · Accepted Answer · answered Feb 08 '13 at 19:51

You can choose to specify which columns you want to insert into in an insert statement.

$sql = "INSERT INTO testTable(ID, ip, date, Title)
VALUES('$ID','".$_SERVER['REMOTE_ADDR']."',NOW(),'Page1')";

Additionally, please don't use mysql functions as they are deprecated now. Use MySQLi, or PDO

score 3 · Answer 3 · answered Feb 08 '13 at 19:50

You have to specify the fields with the second query. If you're not going to insert every column, in the order of the columns, then you have to specify the column names.

INSERT INTO table (column1, column2, columns3) VALUES ('$value1', '$value2', '$value3');

score 1 · Answer 4 · answered Feb 08 '13 at 19:53

1

You can use a column list or SET syntax

Column list:

INSERT INTO table (column1, column2) VALUES ('$value1', '$value2');

SET syntax:

INSERT INTO table SET column1 = '$value1', column2 = '$value2';

answered Feb 08 '13 at 19:53

user20232359723568423357842364

4,624
1
18
28

score 1 · Answer 5 · answered Feb 08 '13 at 20:20

1

In first query error not comes because you are specifying all column and fieleds.If any filed is auto increment or by default null you should mention all the column name along with values in insert query accepting null of auto increment field

this are demo with

All field value

insert into testtable values (1,"127.1.1.0",curdate(),"test 1","default");

Without Default value

insert into testtable (id,ip,date,title) values (1,"127.1.1.0",curdate(),"test 1");

without auto increment field

insert into testtable (ip,date,title) values ("127.1.1.0",curdate(),"test 1");

answered Feb 08 '13 at 20:20

Bhaskar Bhatt

1,399
13
19

Just to make this clearer. You need to add default fields as well if you want to avoid writing the columns after the `into`. For example, if the table is set to add a timestamp in the last column by default, it is not enough to write all values but the last one without writing all of the columns you want to fill after the `into`. You still need to enter the timestamp itself in the values. You cannot just leave it out and still not write down all of the columns that you are going to fill after the `into`. – questionto42 May 15 '22 at 21:20

Does MySQL Insert allow not specifying all fields?

5 Answers5