Given the output of `show variables like '%version%';`, how do I determine what syntax I should use?

Question

I sometimes get strange SQL errors, which tell me to check my MySQL version, e.g.

mysql> CREATE TABLE city (
    ->     id int  NOT NULL IDENTITY(1, 1),
    ->     city_name char(128)  NOT NULL,
    ->     lat decimal(9,6)  NOT NULL,
    ->     long decimal(9,6)  NOT NULL,
    ->     country_id int  NOT NULL,
    ->     CONSTRAINT city_pk PRIMARY KEY  (id)
    -> );
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 'IDENTITY(1, 1),
    city_name char(128)  NOT NULL,
    lat decimal(9,6)  NOT NUL' at line 2

So I do:

mysql> show variables like '%version%';
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| admin_tls_version        | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.27                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| replica_type_conversions |                               |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.27-0ubuntu0.20.04.1       |
| version_comment          | (Ubuntu)                      |
| version_compile_machine  | x86_64                        |
| version_compile_os       | Linux                         |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+

The rest of the error message tells me to check a manual, but I'm not sure which to check. All that this output tells me is that I'm using version 8.0.27 of something unspecified. So, by the books, what should my next step be for discovering my original syntax error?

that's mysql 8.0.27, mysql syntax stays same, only administrative features may change a bit and some features may be added — num8er, Oct 30 '21 at 18:51
change id field to this: `id int(11) unsigned NOT NULL AUTO_INCREMENT`, — num8er, Oct 30 '21 at 18:52

score 2 · Accepted Answer · answered Oct 30 '21 at 18:53

IDENTITY() is not supported by MySQL. That's a Microsoft SQL Server thing (and Sybase).

You want to use AUTO_INCREMENT to do a similar feature of allocating monotonically increasing integers for the primary key.

Read about it here: https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

Given the output of `show variables like '%version%';`, how do I determine what syntax I should use?

1 Answers1