gpt4 book ai didi

mysql - 将另一个表导入数据库并添加字段

转载 作者:行者123 更新时间:2023-11-30 22:15:44 25 4
gpt4 key购买 nike

我有一个 csv 文件,我想将其导入到我的数据库中。但是,csv 文件中的列与我要将其导入的表中的列不匹配。我想做两件事:

  1. 导入表格但只导入部分列
  2. 为我正在导入的表中没有的列提供默认值。

我希望我对此解释得足够好。不太确定如何为这个问题添加任何“代码”,但我已经放入了一些图像。

这是我现有的表:

Columns in existing table

这是我要导入的 csv 文件的列

enter image description here

我想以某种方式只将此 csv 文件的名称(作为标题)、slug 和 created_at、updated_at 导入到我的表中。此外,我希望自动添加 ID,并将“exam_id”和“popular” bool 值默认设置为 0。

最佳答案

LOAD DATA INFILE 是您所需要的。

仔细阅读:http://dev.mysql.com/doc/refman/5.7/en/load-data.html .

对你来说最有趣的部分是:

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata
(col1,col2,...);

You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.

The column list can contain either column names or user variables. With user variables, the SET clause enables you to perform transformations on their values before assigning the result to columns.

User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2:

LOAD DATA INFILE 'file.txt'   INTO TABLE t1   (column1, @var1)   SET
column2 = @var1/100;

The SET clause can be used to supply values not derived from the input file. The following statement sets column3 to the current date and time:

LOAD DATA INFILE 'file.txt'   INTO TABLE t1   (column1, column2)   SET
column3 = CURRENT_TIMESTAMP;

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'   INTO TABLE t1   (column1, @dummy,
column2, @dummy, column3);

关于mysql - 将另一个表导入数据库并添加字段,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38342174/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com