mysql - 通过windows客户端将数据加载到linux托管的mysql中

Question

我正在运行最新版本的 Workbench。

我在数据库中创建了一个表:

use medicare_dataset;
CREATE TABLE `medicarepuf` (
  `npi` int(10) NOT NULL,
  `nppes_provider_last_org_name` varchar(70) DEFAULT NULL,
  `nppes_provider_first_name` varchar(20) DEFAULT NULL,
  `nppes_provider_mi` varchar(1) DEFAULT NULL,
  `nppes_credentials` varchar(20) DEFAULT NULL,
  `nppes_provider_gender` varchar(1) DEFAULT NULL,
  `nppes_entity_code` varchar(1) DEFAULT NULL,
  `nppes_provider_street1` varchar(55) DEFAULT NULL,
  `nppes_provider_street2` varchar(55) DEFAULT NULL,
  `nppes_provider_city` varchar(40) DEFAULT NULL,
  `nppes_provider_zip` int(20) DEFAULT NULL,
  `nppes_provider_state` varchar(2) DEFAULT NULL,
  `nppes_provider_country` varchar(2) DEFAULT NULL,
  `provider_type` varchar(43) DEFAULT NULL,
  `medicare_participation_indicator` varchar(1) DEFAULT NULL,
  `place_of_service` varchar(1) DEFAULT NULL,
  `hcpcs_code` varchar(5) DEFAULT NULL,
  `hcpcs_description` varchar(30) DEFAULT NULL,
  `line_srvc_cnt` varchar(10) DEFAULT NULL,
  `bene_unique_cnt` varchar(10) DEFAULT NULL,
  `bene_day_srvc_cnt` varchar(10) DEFAULT NULL,
  `average_Medicare_allowed_amt` varchar(20) DEFAULT NULL,
  `stdev_Medicare_allowed_amt` varchar(20) DEFAULT NULL,
  `average_submitted_chrg_amt` varchar(20) DEFAULT NULL,
  `stdev_submitted_chrg_amt` varchar(20) DEFAULT NULL,
  `average_Medicare_payment_amt` varchar(20) DEFAULT NULL,
  `stdev_Medicare_payment_amt` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(一个侧面，但我不确定为什么 mysql 在定义列名时需要 ` 字符而不是单引号 ''')

我使用以下 mysql shell 脚本尝试将 70mb 文件加载到表中:

LOAD DATA INFILE 'C:/Users/agordon/Downloads/Medicare_Physician_and_Other_Supplier_PUF_aa_CY2012.csv'
INTO TABLE medicarepuf
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

发生的情况是这样的:

这是正在发生的事情的视频剪辑:http://screencast.com/t/ReFfyQxtAJO

这是我试图获取的几行原始数据:

+------------+------------------------------+---------------------------+-------------------+-------------------+-----------------------+-------------------+------------------------+------------------------+---------------------+--------------------+----------------------+------------------------+---------------+----------------------------------+------------------+------------+------------------------------+-----------------+-------------------+---------------------+------------------------------+----------------------------+----------------------------+--------------------------+------------------------------+----------------------------+
|    npi     | nppes_provider_last_org_name | nppes_provider_first_name | nppes_provider_mi | nppes_credentials | nppes_provider_gender | nppes_entity_code | nppes_provider_street1 | nppes_provider_street2 | nppes_provider_city | nppes_provider_zip | nppes_provider_state | nppes_provider_country | provider_type | medicare_participation_indicator | place_of_service | hcpcs_code |      hcpcs_description       |  line_srvc_cnt  |  bene_unique_cnt  |  bene_day_srvc_cnt  | average_Medicare_allowed_amt | stdev_Medicare_allowed_amt | average_submitted_chrg_amt | stdev_submitted_chrg_amt | average_Medicare_payment_amt | stdev_Medicare_payment_amt |
+------------+------------------------------+---------------------------+-------------------+-------------------+-----------------------+-------------------+------------------------+------------------------+---------------------+--------------------+----------------------+------------------------+---------------+----------------------------------+------------------+------------+------------------------------+-----------------+-------------------+---------------------+------------------------------+----------------------------+----------------------------+--------------------------+------------------------------+----------------------------+
| 1003002494 | ANDERSON                     | JOSEPH                    | M                 | M.D.              | M                     | I                 | 802 B ST               |                        | SAN RAFAEL          |          949013026 | CA                   | US                     | Pathology     | Y                                | O                | 88305      | Tissue exam by pathologist   | 1,797           |               165 |                 168 | 133.43                       | 20.95                      | 263.23                     | 41.17                    | 105.53                       | 25.87                      |
| 1003002494 | ANDERSON                     | JOSEPH                    | M                 | M.D.              | M                     | I                 | 802 B ST               |                        | SAN RAFAEL          |          949013026 | CA                   | US                     | Pathology     | Y                                | O                | 88342      | Immunohistochemistry         | 568             |                72 |                  73 | 131.74                       | 16.22                      | 255.64                     | 31.27                    | 102.43                       | 39.47                      |
| 1003002494 | ANDERSON                     | JOSEPH                    | M                 | M.D.              | M                     | I                 | 802 B ST               |                        | SAN RAFAEL          |          949013026 | CA                   | US                     | Pathology     | Y                                | O                | G0416      | Sat biopsy prostate 1-20 spc | 25              |                25 |                  25 | 866.27                       | 0                          | 1733                       | 0                        | 693.02                       | 0                          |
+------------+------------------------------+---------------------------+-------------------+-------------------+-----------------------+-------------------+------------------------+------------------------+---------------------+--------------------+----------------------+------------------------+---------------+----------------------------------+------------------+------------+------------------------------+-----------------+-------------------+---------------------+------------------------------+----------------------------+----------------------------+--------------------------+------------------------------+----------------------------+

我做错了什么？如何将数据加载到表中？

Answer 1

尝试

LOAD DATA LOCAL INFILE

我认为问题是您正在尝试将 Windows 文件加载到 Linux mysql。这意味着该文件对于客户端来说是本地。