在 MySQL 中导入 50K+ 记录会出现一般错误:1390 Prepared statement contains too many placeholders [英] Import of 50K+ Records in MySQL Gives General error: 1390 Prepared statement contains too many placeholders

问题描述

有没有人遇到过这个错误:一般错误:1390 Prepared statement contains too many placeholders

Has anyone ever come across this error: General error: 1390 Prepared statement contains too many placeholders

我刚刚通过 SequelPro 导入了超过 50,000 条记录，现在当我在视图中查看这些记录 (Laravel 4) 时出现一般错误:1390 Prepared statement contains too many placeholders.

I just did an import via SequelPro of over 50,000 records and now when I go to view these records in my view (Laravel 4) I get General error: 1390 Prepared statement contains too many placeholders.

我的 AdminNotesController.php 文件中的以下 index() 方法是生成查询和呈现视图的内容.

The below index() method in my AdminNotesController.php file is what is generating the query and rendering the view.

public function index()
{
    $created_at_value = Input::get('created_at_value');
    $note_types_value = Input::get('note_types_value');
    $contact_names_value = Input::get('contact_names_value');
    $user_names_value = Input::get('user_names_value');
    $account_managers_value = Input::get('account_managers_value');

    if (is_null($created_at_value)) $created_at_value = DB::table('notes')->lists('created_at');
    if (is_null($note_types_value)) $note_types_value = DB::table('note_types')->lists('type');
    if (is_null($contact_names_value)) $contact_names_value = DB::table('contacts')->select(DB::raw('CONCAT(first_name," ",last_name) as cname'))->lists('cname');
    if (is_null($user_names_value)) $user_names_value = DB::table('users')->select(DB::raw('CONCAT(first_name," ",last_name) as uname'))->lists('uname');

    // In the view, there is a dropdown box, that allows the user to select the amount of records to show per page. Retrieve that value or set a default.
    $perPage = Input::get('perPage', 10);

    // This code retrieves the order from the session that has been selected by the user by clicking on a table column title. The value is placed in the session via the getOrder() method and is used later in the Eloquent query and joins.
    $order = Session::get('account.order', 'company_name.asc');
    $order = explode('.', $order);

    $notes_query = Note::leftJoin('note_types', 'note_types.id', '=', 'notes.note_type_id')
        ->leftJoin('users', 'users.id', '=', 'notes.user_id')
        ->leftJoin('contacts', 'contacts.id', '=', 'notes.contact_id')
        ->orderBy($order[0], $order[1])
        ->select(array('notes.*', DB::raw('notes.id as nid')));

    if (!empty($created_at_value)) $notes_query = $notes_query->whereIn('notes.created_at', $created_at_value);

    $notes = $notes_query->whereIn('note_types.type', $note_types_value)
        ->whereIn(DB::raw('CONCAT(contacts.first_name," ",contacts.last_name)'), $contact_names_value)
        ->whereIn(DB::raw('CONCAT(users.first_name," ",users.last_name)'), $user_names_value)
        ->paginate($perPage)->appends(array('created_at_value' => Input::get('created_at_value'), 'note_types_value' => Input::get('note_types_value'), 'contact_names_value' => Input::get('contact_names_value'), 'user_names_value' => Input::get('user_names_value')));

    $notes_trash = Note::onlyTrashed()
        ->leftJoin('note_types', 'note_types.id', '=', 'notes.note_type_id')
        ->leftJoin('users', 'users.id', '=', 'notes.user_id')
        ->leftJoin('contacts', 'contacts.id', '=', 'notes.contact_id')
        ->orderBy($order[0], $order[1])
        ->select(array('notes.*', DB::raw('notes.id as nid')))
        ->get();

    $this->layout->content = View::make('admin.notes.index', array(
        'notes'             => $notes,
        'created_at'        => DB::table('notes')->lists('created_at', 'created_at'),
        'note_types'        => DB::table('note_types')->lists('type', 'type'),
        'contacts'          => DB::table('contacts')->select(DB::raw('CONCAT(first_name," ",last_name) as cname'))->lists('cname', 'cname'),
        'accounts'          => Account::lists('company_name', 'company_name'),
        'users'             => DB::table('users')->select(DB::raw('CONCAT(first_name," ",last_name) as uname'))->lists('uname', 'uname'),
        'notes_trash'       => $notes_trash,
        'perPage'           => $perPage
    ));
}

任何建议将不胜感激.谢谢.

Any advice would be appreciated. Thanks.

推荐答案

使用 array_chunk 函数解决了这个问题.

Solved this issue by using array_chunk function.

解决方法如下:

foreach (array_chunk($data,1000) as $t)  
{
     DB::table('table_name')->insert($t); 
}

        

这篇关于在 MySQL 中导入 50K+ 记录会出现一般错误:1390 Prepared statement contains too many placeholders的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！