如何同时在表中插入多条记录？

I have two tables Accommodation and Facility, which are connected in a many-to-many relationship with a third table, Accommodation_facility.

我有两个表住宿和设施，它们与第三个表，Accommodation_facility以多对多关系连接。

Accommodation (accommodation_id, accommodation_type, name)
住宿（住宿_id，住宿类型，名称）
Facility (facility_id, facility_name)
设施（facility_id，facility_name）
Accommodation_facility (accommodation_id, facility_id)
Accommodation_facility（accommodation_id，facility_id）

Using Yii, how can you insert multiple records of data into the Accomodation_facility table?

使用Yii，如何将多个数据记录插入到Accomodation_facility表中？

5 个解决方案

#1

Inserting using a loop is very slow. Let's say you have 5000 rows to insert, it's going to take around 6 minutes that way (separate insert for each record). It's better to insert the data with a single query:

使用循环插入非常慢。假设您要插入5000行，这将花费大约6分钟（每个记录单独插入）。最好使用单个查询插入数据：

$values = '(null, "your string"), (null, "next string"), (null, "third string")';
$sql = 'INSERT INTO table_data (id, data) VALUES ' . $values;
$command = Yii::app()->db->createCommand($sql);
$command->execute();

That will take 1/10 of the time.

那将需要1/10的时间。

#2

You better have to use bindParam to prevent from SQL injections. I don't know if it is the best way to do that, but there is the way i'm doing this :

您最好使用bindParam来防止SQL注入。我不知道这是否是最好的方法，但我有这样做的方式：

$values = array(array(1,2),array(3,4),array(5,6),);
$nbValues = count($values);
$sql = 'INSERT INTO table_name (col_name1, col_name2) VALUES ';
for ($i=0; $i < $nbValues; $i++) { 
    $sql .= '(:col1_'.$i.', :col2_'.$i.')';
    if ($i !== ($nbValues-1))
        $sql .= ',';
}
$command = Yii::app()->db->createCommand($sql);
for ($i=0; $i < $nbValues; $i++) { 
    $command->bindParam(':col1_'.$i, $values[$i][0], PDO::PARAM_INT);
    $command->bindParam(':col2_'.$i, $values[$i][1], PDO::PARAM_INT);
}
$command->execute();

Hope this helps !

希望这可以帮助！

#3

foreach($facilities as $facility)
{ 
    $model = new Model;
    $model->attributes = $facility // or $model->column = $facility
    if ($model->validate())
        $model->save() 
}

#4

//only for 1.1.14
$builder = Yii::app()->db->schema->commandBuilder;
$command=$builder->createMultipleInsertCommand('tbl_post', array(
  array('title' => 'record 1', 'text' => 'text1'),
  array('title' => 'record 2', 'text' => 'text2'),
));
$command->execute();

http://www.yiiframework.com/news/72/yii-1-1-14-release-candidate-is-available/

#5

-2

Since your question is tagged in "yii" I guess you are using Yii Framework. Take a look at Active Records over at the docs - http://www.yiiframework.com/doc/guide/1.1/en/database.ar

由于您的问题标记为“yii”，我猜您使用的是Yii Framework。在文档中查看Active Records - http://www.yiiframework.com/doc/guide/1.1/en/database.ar

Follow the docs to set up AR classes for your tables, and simply loop over the data you post when you submit your checkboxlist. In this loop you create, populate and save the AR objects for the tables you wish to insert data for.

按照文档为您的表设置AR类，并简单地循环您提交复选框列表时发布的数据。在此循环中，您可以为要插入数据的表创建，填充和保存AR对象。

#1