SQL语法错误求助：Shell脚本中MySQL报ERROR 1064(42000)

Hey there! I’ve run into this exact error a few times when mixing shell scripts and MySQL, so let’s break down what’s going wrong and fix it step by step.

The Main Issues Triggering the Error #

Your ERROR 1064 points directly to line 3 (the CREATE TABLE statement) — here are the key problems:

• Invalid MySQL data type: NUM isn’t a recognized data type in MySQL. For the filesize field, you’ll need to use a valid type like INT (for whole-number file sizes) or DECIMAL(10,2) if you need to track sizes with decimals. This is the immediate cause of your syntax error.
• Here-doc formatting quirk: When using <<-EOFMYSQL, the closing EOFMYSQL marker must be indented with a tab character (not spaces). If you use spaces instead, MySQL won’t recognize where the here-doc ends, which can also throw syntax errors.
• Crammed SQL statements: While MySQL allows multiple statements on one line, packing everything together makes it easy to miss small syntax mistakes. Adding line breaks and proper indentation helps catch issues faster.

Corrected Script Example #

Here’s your script with all fixes applied:

mysql -u root -p'password' <<-EOFMYSQL
CREATE DATABASE IF NOT EXISTS KF4005AL;
USE KF4005AL;
CREATE TABLE filedata (
    filename VARCHAR(20),
    userid VARCHAR(20),
    groupid VARCHAR(20),
    permissions VARCHAR(20),
    filesize INT, -- Swapped invalid NUM for valid INT type
    lastaccessdate DATE,
    lastaccesstime TIME,
    lastmodification TIME,
    creationdate DATE,
    creationtime TIME
);
-- Finish your LOAD DATA statement here (example included):
-- LOAD DATA LOCAL INFILE '/path/to/your/file.txt' 
-- INTO TABLE filedata 
-- FIELDS TERMINATED BY '\t' 
-- LINES TERMINATED BY '\n';
EOFMYSQL

Extra Tips to Avoid Future Headaches #

• Secure your password: If you’re using MySQL 8.0+, passing the password directly in the command line (-p'password') is a security risk. Instead, create a .my.cnf file with your credentials and use --defaults-extra-file=/path/to/.my.cnf in your script.
• Test SQL separately: Before embedding SQL in a shell script, run it directly in the MySQL command line. This lets you isolate syntax errors without dealing with shell script quirks.
• Double-check LOAD DATA syntax: Make sure your LOAD DATA statement includes all necessary details (like field separators, line endings, and whether the file has a header row) — incomplete LOAD DATA commands can also trigger 1064 errors.

内容的提问来源于stack exchange，提问作者Therealdeal