如何在windows上运行sql脚本？

Question

我试过了

mysqlsh -h localhost -u mixtape-dating -p -f setup.sql

而setup.sql由以下部分组成

USE MixtapeDating;

-- Tables
-- The Playlist table gets featured on the front page. 
CREATE TABLE Playlist (
    Id INT PRIMARY KEY AUTO_INCREMENT, 
    Title VARCHAR(255),
    Email VARCHAR(255),
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- The PlaylistItem gets featured at /playlist/:id
CREATE TABLE PlaylistItem (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    PlaylistId INT, 
    Title VARCHAR(255),
    Link VARCHAR(255)
);

-- Stored Procedures
-- Insert playlist
CREATE PROCEDURE InsertPlaylist
(
    IN Title VARCHAR(255),
    IN Email VARCHAR(255),
    OUT PlaylistId INT
)
BEGIN
    INSERT INTO Playlist (Title, Email)
    VALUES (@Title, @Email);
    SELECT LAST_INSERT_ID() AS @PlaylistId;
END

CREATE PROCEDURE InsertPlaylistItem
(
    IN PlaylistId INT, 
    IN Title VARCHAR(255),
    IN Link VARCHAR(255)
)
BEGIN
    INSERT INTO PlaylistItem (PlaylistId, Title, Link)
    VALUES (@PlaylistId, @Title, @Link);
END

-- Fetch all playlists
CREATE PROCEDURE GetPlaylists
BEGIN
    SELECT Id, Title, Email 
    FROM Playlist;
END

-- Fetch all playlist items for a playlist
CREATE PROCEDURE GetPlaylist
(IN Id INT)
BEGIN
    SELECT PlaylistId, Id, Title, Link
    FROM PlaylistItem
    WHERE Id = @Id; 
END

但我得到以下错误：

C:\Users\moore\Desktop\playlist-dating>mysqlsh -h localhost -u mixtape-dating -p -f setup.sql
Enter password: ****************
SyntaxError: Unexpected identifier at setup.sql:1:4
in USE MixtapeDating;
       ^^^^^^^^^^^^^

我做错了什么？

Answer 1

mysqlsh在javascript模式下启动default。如果要运行SQL语句，则需要切换到SQL模式。您可以在mysqlsh中添加--sql参数来完成此操作。

mysqlsh -h localhost -u mixtape-dating -p -f setup.sql --sql

Answer 2

应该能够做到：

mysql -u username -p databasename < file.sql

我不知道我有没有见过mysqlsh被用于这个目的？

Answer 3

您的setup.sql有一些问题。首先，缺省分隔符是;，因此shell部分地解释您的create procedure。将您的分隔符更改为其他分隔符，例如更改为//。另外，以@开头的变量是User-Defined Variables，不适用于在过程内部使用。

修复了setup.sql。您可以使用：mysqlsh -h localhost -u mixtape-dating -p -f setup.sql运行此脚本。

-- DROP SCHEMA IF EXISTS MixtapeDating;
-- CREATE SCHEMA MixtapeDating;
USE MixtapeDating;

-- Tables
-- The Playlist table gets featured on the front page. 
CREATE TABLE Playlist (
    Id INT PRIMARY KEY AUTO_INCREMENT, 
    Title VARCHAR(255),
    Email VARCHAR(255),
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- The PlaylistItem gets featured at /playlist/:id
CREATE TABLE PlaylistItem (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    PlaylistId INT, 
    Title VARCHAR(255),
    Link VARCHAR(255)
);

DELIMITER //

-- Stored Procedures
-- Insert playlist
CREATE PROCEDURE InsertPlaylist
(
    IN Title_ VARCHAR(255),
    IN Email_ VARCHAR(255),
    OUT PlaylistId_ INT
)
BEGIN
    INSERT INTO Playlist (Title, Email)
    VALUES (Title_, Email_);
    SELECT LAST_INSERT_ID() AS PlaylistId_;
END
//

CREATE PROCEDURE InsertPlaylistItem
(
    IN PlaylistId_ INT, 
    IN Title_ VARCHAR(255),
    IN Link_ VARCHAR(255)
)
BEGIN
    INSERT INTO PlaylistItem (PlaylistId, Title, Link)
    VALUES (PlaylistId_, Title_, Link_);
END
//

-- Fetch all playlists
CREATE PROCEDURE GetPlaylists()
BEGIN
    SELECT Id, Title, Email 
    FROM Playlist;
END
//

-- Fetch all playlist items for a playlist
CREATE PROCEDURE GetPlaylist (IN Id_ INT)
BEGIN
    SELECT PlaylistId, Id, Title, Link
    FROM PlaylistItem
    WHERE Id = Id_;
END
//