时间戳

首页 > 解决方案 > MySQL根据第一行加载数据

php - MySQL根据第一行加载数据

问题描述

有没有办法从 CSV 文件中加载数据，其中第一行是列名，其他行是数据？

我尝试了简单的查询，但它插入时没有任何排序

询问 ：

$q = "LOAD DATA LOCAL INFILE '".$filepath.$filename_update."' 
    INTO TABLE ".$dbName.$tableName."_uimport_update
    FIELDS TERMINATED BY ';'
    ENCLOSED BY '\"'
    IGNORE 1 LINES;";

数据库列

product_id, product_sku, ...

CSV 文件

mdate, product_sku, ....

结果不好：

product_id, product_sku, ...
mdate, product_sku, ....

想要的结果：

product_id, product_sku, ...
product_id, product_sku, ....

标签： phpmysqlsql

解决方案

您可以指定查询中列的顺序。我建议在查询中添加一个字符集，并为附件添加一个“可选”。

但是让我们看一下：

CREATE TABLE `foo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `bar` varchar(255) DEFAULT NULL,
  `baz` varchar(255) DEFAULT NULL,
  `bla` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

vagrant@fancyhost:~$ cat foo.csv

酒吧;baz;bla

“测试 bndg”；baaaaaaaaaaaaz；“yeyo”

“我 looooooove 虚拟数据”；调试；虚拟

测试1;测试2;测试3

mysql> LOAD DATA INFILE '/home/vagrant/foo.csv' INTO TABLE foo CHARACTER SET utf8 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (bar,baz,bla)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM foo;
+----+-------------------------+----------------+-------+
| id | bar                     | baz            | bla   |
+----+-------------------------+----------------+-------+
|  1 | test bndg               | baaaaaaaaaaaaz | yeyo  |
|  2 | I looooooove dummy data | debug          | dummy |
|  3 | test1                   | test2          | test3 |
+----+-------------------------+----------------+-------+
3 rows in set (0.00 sec)

mysql> LOAD DATA INFILE '/home/vagrant/foo.csv' INTO TABLE foo CHARACTER SET utf8 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (bar,bla,baz)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM foo;
+----+-------------------------+----------------+----------------+
| id | bar                     | baz            | bla            |
+----+-------------------------+----------------+----------------+
|  1 | test bndg               | baaaaaaaaaaaaz | yeyo           |
|  2 | I looooooove dummy data | debug          | dummy          |
|  3 | test1                   | test2          | test3          |
|  4 | test bndg               | yeyo           | baaaaaaaaaaaaz |
|  5 | I looooooove dummy data | dummy          | debug          |
|  6 | test1                   | test3          | test2          |
+----+-------------------------+----------------+----------------+
6 rows in set (0.00 sec)

您可以根据您的示例轻松更改订单:)

推荐阅读