MySql:[ERR] 1064 You have an error in your SQL syntax

description¶

Problems with using MySql to build tables

When using Navicat Premium to run sql statement to build a table, MySQL reports the following error:

# Table building statement:

DROP DATABASE IF EXISTS javaweb;
CREATE DATABASE javaweb DEFAULT CHARACTER SET utf8;
USE javaweb;

CREATE TABLE user(   
    id int primary key auto_increment,
    name varchar(20) not null,
    gender varchar(5),
    age int,
    address varchar(32),
    qq    varchar(20),
    email varchar(50),
    username varchar(32),
    password varchar(32)
);

# error message
[ERR] 1064 - You have an error in your SQL syntax; check the manual.......

solution¶

This problem is a grammatical error in MySql. In MySQL, in order to distinguish MySQL keywords from ordinary characters, MySQL introduced a backtick.
In the above sql statement, if the column name does not use backquotes or the column name uses single quotation marks, this error will be reported.

DROP DATABASE IF EXISTS javaweb;
CREATE DATABASE javaweb DEFAULT CHARACTER SET utf8;
USE javaweb;

CREATE TABLE user(   
    `id` int primary key auto_increment,
    `name` varchar(20) not null,
    `gender` varchar(5),
    `age` int,
    `address` varchar(32),
    `qq`    varchar(20),
    `email` varchar(50),
    `username` varchar(32),
    `password` varchar(32)
);