MYSQL INSERT INTO syntax error

Question

I would like to insert a new row in the following table:

+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| index             | bigint(20) unsigned | NO   | PRI | NULL    |       |
| exports_fields_id | bigint(20) unsigned | NO   | PRI | NULL    |       |
| exports_id        | bigint(20) unsigned | NO   | MUL | NULL    |       |
+-------------------+---------------------+------+-----+---------+-------+

Therefor I tried the following sql statement:

INSERT INTO 'exports_has_export_fields' ('index', 'exports_fields_id', 'exports_id') VALUES (0, 78, 3);

But then I get the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''exports_has_export_fields' ('index', 'exports_fields_id', 'exports_id') VALUES ' at line 1

Community · Accepted Answer · 2017-05-23 12:33:00Z

You are using single quotes, but you need backticks (`) to refer to fields or tables whose name may conflict with MySQL reserved keywords.

In this case, just index is a reserved one, even though you can backtick everything for safety sake.

So you can say:

INSERT INTO `exports_has_export_fields` (`index`, `exports_fields_id`, `exports_id`) VALUES (0, 78, 3);
            ^                         ^  ^     ^  ^                 ^  ^          ^

or just

INSERT INTO exports_has_export_fields (`index`, exports_fields_id, exports_id) VALUES (0, 78, 3);
                                       ^     ^

From When to use single quotes, double quotes, and backticks?:

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

hp2p · Accepted Answer · 2015-08-20 10:13:49Z

1

I think field index is primary key and it not zero try

SET sql_mode='NO_AUTO_VALUE_ON_ZERO';

before inserting the 0 value.

answered Aug 20, 2015 at 10:13

hp2p

111 bronze badge

Comments

Ni Nö · Accepted Answer · 2015-08-20 10:13:56Z

1

Try it with (edit with backticks)

INSERT INTO `exports_has_export_fields` (`index`, `exports_fields_id`, `exports_id`) VALUES (0, 78, 3);

There shouldn't be single quotes on table and column names.

If you are inserting in all Columns of a Table you can shorten your Insert statement like this:

INSERT INTO exports_has_export_fields VALUES (0, 78, 3);

edited Aug 20, 2015 at 10:13

answered Aug 20, 2015 at 10:06

Ni Nö

517 bronze badges

3 Comments

Bene Over a year ago

Thanks for the answer with the shorter statement. The one above fails aswell. Only the shorten statement insert a new row. You know with it fails?

Ni Nö Over a year ago

@user3205343 : try it with the backticks as mentioned in the answers above.

fedorqui Over a year ago

this doesn't work because index is a reserved keyword in MySQL.

Daniel Hernandez · Accepted Answer · 2015-08-20 10:16:03Z

1

Have you tried removing the single quotes from your table name and fields?

INSERT INTO exports_has_export_fields (`index`, exports_fields_id, exports_id) VALUES (0, 78, 3);

Or if you want to shorten your query, try

INSERT INTO exports_has_export_fields VALUES (0, 78, 3);

edited Aug 20, 2015 at 10:16

answered Aug 20, 2015 at 10:06

Daniel Hernandez

2291 silver badge8 bronze badges

Comments

Valery Viktorovsky · Accepted Answer · 2015-08-20 10:16:05Z

1

index is a reserved word. Quote it (with backticks).

So you should use backticks quotes ` instead of single quotes '.

INSERT INTO `exports_has_export_fields` (`index`, `exports_fields_id`, `exports_id`) VALUES (0, 78, 3);

edited Aug 20, 2015 at 10:16

answered Aug 20, 2015 at 10:08

Valery Viktorovsky

6,7863 gold badges47 silver badges52 bronze badges

Collectives™ on Stack Overflow

MYSQL INSERT INTO syntax error

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related