insert statement from mysql 5.5.68 not working in 8.0.31

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!

>Solution :

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()

Leave a Reply