MySQL建表并将csv文件导入表的步骤及所遇问题

MySQL建表并将csv文件导入表的步骤及所遇问题

步骤一：建表dim_bi_productspe_scd

DROP TABLE dim_bi_productspe_scd;
CREATE TABLE IF NOT EXISTS dim_bi_productspe_scd(
      BIProductSpeKey            BIGINT
     ,BIProductSpeID             BIGINT       
     ,ProductSpecId              BIGINT       
     ,ownerType                     INT       
     ,ownerid                    BIGINT       
     ,CityId                        INT       
     ,RealWarehouseId               INT       
     ,TotalCount_MinUnit         DOUBLE       
     ,TotalCount_MaxUnit         DOUBLE       
     ,CreateTime              TIMESTAMP      
     ,lastupdatetime          TIMESTAMP       


     ,ProductInfoID                BIGINT      
     ,SpeName                      VARCHAR(100)      
     ,SpeInfoMaxUnit               VARCHAR(100)     
     ,SpeInfoMinUnit               VARCHAR(100)      
     ,SpeInfoQuantity              DOUBLE      

     ,BrandName                    VARCHAR(100)      
     ,StatisticsCategoryName       VARCHAR(100)      
     ,productInfoName              VARCHAR(100)      
     ,OriginalPlace                VARCHAR(100)      
     ,ProductInfoStatusID             INT      
     ,ProductInfoStatus            VARCHAR(100)      
     ,BottleCode                   VARCHAR(100)      
     ,ProductCode                  VARCHAR(100)      
     ,packagingCode                VARCHAR(100)      
     ,ProductInfoTypeID               INT      
     ,ProductInfoType              VARCHAR(100)      
     ,ProductStatisticsClass       BIGINT      
     ,SecondStatisticsClass        BIGINT     
     ,ShopId                       BIGINT      
     ,ShelfLifeLongTime           TINYINT     
     ,PackageType                 TINYINT      
     ,IsProcess                   TINYINT      
     ,StorageType                 TINYINT      

     ,ERPMinUnitCostPrice         DOUBLE       
     ,ERPMaxUnitCostPrice         DOUBLE       

     ,FirstDisPlayCategoryId         INT       
     ,FirstDisPlayCategory        VARCHAR(100)       
     ,SecondDisPlayCategoryId        INT       
     ,SecondDisPlayCategory       VARCHAR(100)       
     ,ThirdDisPlayCategoryId         INT       
     ,ThirdDisPlayCategory        VARCHAR(100)       
     ,BrandID                       INT      
     ,ProductBusinessClassID        INT       
     ,ProductBusinessClass       VARCHAR(100)       
     ,UnitPriceClassID              INT       
     ,UnitPriceClass             VARCHAR(100)       

    ,IsValid                     TINYINT       
    ,ValidFrom                 TIMESTAMP       
    ,ValidTo                   TIMESTAMP       
    ,ETLCreateTime             TIMESTAMP      
    ,ETLLastUpdateTime         TIMESTAMP       
);

• 问题一：非常小白了。之前建kudu和hive表格时，字符串类型写的是 STRING，但是发现mysql并不支持，会报错：
  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'string

• 解决办法 ：把STRING更改为VARCHAR(100)

步骤二 将csv文件导入表dim_bi_productspe_scd

LOAD DATA INFILE 'D:/mysource/query-impala-95151.csv' 
INTO TABLE dim_bi_productspe_scd
FIELDS TERMINATED BY ',' --OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n' IGNORE  1 LINES;

• TERMINATED用于控制字段的分隔符，可以为多个字符。

• ENCLOSED BY用于用于控制字段的引号（统一by后字段的格式），必须为单一字符,如果忽略了词语OPTIONALLY，则所有的字段都被包含在ENCLOSED BY字符串中,如果指定了OPTINALLY，则ENCLOSED BY字符只被用于包含具有字符串数据类型（比如CHAR, BINARY, TEXT或ENUM）的列中的值.

• ESCAPED BY用于转义。如果FIELDS ESCAPED BY字符为空字符，则没有字符被转义，并且NULL被作为NULL输出，而不是\N。

• 问题二 MYSQL导入CSV格式文件数据执行提示错误（ERROR 1290）
  The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.

• 因为在安装MySQL的时候限制了导入与导出的目录权限。只允许在规定的目录下才能导入。

• 可以通过以下命令查看secure-file-priv当前的值是什么

SHOW VARIABLES LIKE "secure_file_priv";

• 执行命令后发现 value=null，表示禁止。当然，如果value值有文件夹目录，则表示只允许该目录下文件；如果为空，则表示不限制目录。经查资料，解决办法如下：
• 解决办法
  • 针对value=null，去掉导入的目录限制。可修改mysql配置文件（Windows下为my.ini, Linux下的my.cnf），在[mysqld]下面，查看是否有：secure_file_priv =这样一行内容，如果没有，则手动添加。但是我发现我安装mysql的时候并没有my.ini配置文件。
  • 针对没有my.ini配置文件的问题，重新建一个my.ini配置文件，文件内容仅仅只添加secure_file_priv = 重启mysql。发现问题解决。value为空了，表示不限制目录了。
    ————————————————————

重新再运行导入数据的代码，发现又出现另外一个错误○|￣|_

• 问题三 Out of range value for column ‘BIProductSpeKey’ at row 1
• 说的是MySQL在数值列中存储的值超出列数据类型的允许范围，这时取决于当时有效的SQL模式（参考：https://dev.mysql.com/doc/refman/5.6/en/out-of-range-and-overflow.html）：

  • 如果启用了严格的SQL模式，则按照SQL标准，MySQL会拒绝超出范围的值并产生错误，并且插入将失败。

  • 如果未启用任何限制模式，MySQL会将值裁剪到列数据类型范围的适当端点，并存储结果值。当将超出范围的值分配给整数列时，MySQL将存储代表该列数据类型范围的相应端点的值。

• 解决办法：
  • 运行下列代码，使代码未启用严格SQL模式 SET sql_mode = '';
  • 补充知识，启用严格SQL模式的代码是 SET sql_mode = 'TRADITIONAL';