0

This sql statement work perfectly fine in mysql 5.5

INSERT INTO invoices SET LocationsID = "5399", InvoiceDisplayID = "6343088", Generated = NOW()

but now in mysql 8.0 I get the following errors

Static analysis:

3 errors were found during analysis.

Unexpected token. (near "," at position 75)
Unexpected token. (near "Generated" at position 77)
Unrecognized statement type. (near "Generated" at position 77)
SQL query:

INSERT INTO invoices SET LocationsID = "5399", InvoiceDisplayID = "6343088", Generated = NOW()

MySQL said: Documentation

#1064 - 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 'Generated = NOW()' at line 1

it seems that if I modify the sql statement to this

INSERT INTO invoices(
    LocationsID,
    InvoiceDisplayID,
    Generated
)
SELECT
    '5399',
    '6343088',
    NOW()

then it doesn't complain and works fine.

so why is this happening and is there a way to make mysql 8.0 work like 5.5 did for the original insert sql?

thanks!

user713813
  • 775
  • 1
  • 8
  • 20
  • You want `INSERT INTO ... VALUES (...)` : https://dev.mysql.com/doc/refman/8.0/en/insert.html – GMB Dec 30 '22 at 22:34

1 Answers1

1

GENERATED was added as a reserved keyword in MySQL 5.7.6.

You can use backticks to escape it:

INSERT INTO invoices SET ... `Generated` = NOW()
Clive
  • 36,918
  • 8
  • 87
  • 113
  • You could have just closed this as a duplicate of https://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql – Nigel Ren Dec 30 '22 at 22:48
  • Yeah maybe, I wasn't aware of that question. – Clive Dec 30 '22 at 22:53