mysql - 将数据从多个 CSV 加载到 MySQL

Question

我正在尝试将多个 csv 文件加载到 mysql 中以创建这些多个文件的单个表。我在mysql中加载了一个名为symbols的表，其中有一列名为ticker，所有行值都代表A、B、C等符号。我想循环这些行以创建一个路径，/path/A.csv以便我可以阅读来自所有这些文件的数据以创建新表 all_tickers。

之前我在 SQL Server 中使用以下代码执行此操作：

CREATE TABLE dbo.all_tickers
(
     ticker_symbol varchar(32) NOT NULL,
     [Date] datetime2(7) NOT NULL,
     [Open] float NULL,
     High float NULL,
     Low float NULL,
     [close] float NULL,
     Volume float NULL,

     PRIMARY KEY (ticker_symbol, [Date])
);
GO

DECLARE @Path nvarchar(255) = 'D:\ASX';
DECLARE @SQL nvarchar(MAX);

CREATE TABLE #ticker_staging
(
     [Date] datetime2(7) NOT NULL,
     [Open] float NULL,
     High float NULL,
     Low float NULL,
     [close] float NULL,
     Volume float NULL
);

SET @SQL = (SELECT N'
TRUNCATE TABLE #ticker_staging;
BULK INSERT #ticker_staging
    FROM ''' + @Path + N'\' + ticker + N'.csv''
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = '','',  --CSV field delimiter
    ROWTERMINATOR = ''\n'',   --Use to shift the control to next row
    TABLOCK
    );
INSERT INTO dbo.all_tickers WITH (TABLOCKX)(
  ticker_symbol,
  [Date],
  [Open],
  High,
  Low,
  [close],
  Volume
) 
SELECT
  ''' + ticker + N''',
  [Date],
  [Open],
  High,
  Low,
  [close],
  Volume
FROM #ticker_staging;'
FROM dbo.symbols
FOR XML PATH(''), TYPE).value('.',N'nvarchar(MAX)');
EXECUTE sp_executesql @SQL;
GO

为了在 MySQL 中复制结果，我将上面的代码更改为如下：

CREATE TABLE dbo.all_tickers
(
     ticker_symbol varchar(32) NOT NULL,
     Date datetime NOT NULL,
     Open float NULL,
     High float NULL,
     Low float NULL,
     close float NULL,
     Volume float NULL,

     PRIMARY KEY (ticker_symbol, Date)
);

set @Path = 'D:\ASX';
set @SQL = nvarchar(MAX);

CREATE TABLE #ticker_staging 
( 
    Date1 datetime NOT NULL,
    Open float NULL,
    High float NULL,
    Low float NULL,
    close float NULL,
    Volume float NULL
);

SET @SQL = (SELECT N'
TRUNCATE TABLE #ticker_staging;
LOAD DATA INFILE #ticker_staging
    FROM ''' + @Path + N'\' + ticker + N'.csv''
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = '','',  #CSV field delimiter
    ROWTERMINATOR = ''\n'',   #Use to shift the control to next row
    TABLOCK
    );
INSERT INTO dbo.all_tickers WITH (TABLOCKX)(
  ticker_symbol,
  [Date],
  [Open],
  High,
  Low,
  [close],
  Volume
) 
SELECT
  ''' + ticker + N''',
  [Date],
  [Open],
  High,
  Low,
  [close],
  Volume
FROM #ticker_staging;'
FROM dbo.SCRIP
FOR XML PATH(''), TYPE).value('.',N'nvarchar(MAX)');
EXECUTE sp_executesql @SQL;

我在处理WITHSQL Server 中用于将其转换为 MySQL 的子句时遇到问题。

由于我是 MySQL 新手，我认为这是主要问题。如果代码中还有其他问题，请提出建议。

标签： mysqlsql-serverdatabasestock-data