在MySQL中导入50K +记录会出现一般错误:1390 Prepared语句包含太多占位符

问题描述

有人遇到过此错误:一般错误:1390个预处理语句包含太多占位符

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

我刚刚通过SequelPro导入了50,000条以上的记录，现在当我在视图中查看这些记录时(Laravel 4)，出现一般错误:1390 Prepared语句包含太多占位符.

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.

推荐答案

MariaDB 5.5中存在限制 65,535 (2 ^ 16-1)个占位符应该具有与 MySQL 5.5 相同的行为.

There is limit 65,535 (2^16-1) place holders in MariaDB 5.5 which is supposed to have identical behaviour as MySQL 5.5.

不确定是否相关，我使用MySQLi/MySQLND在PHP 5.5.12上对其进行了测试.

Not sure if relevant, I tested it on PHP 5.5.12 using MySQLi / MySQLND.

这篇关于在MySQL中导入50K +记录会出现一般错误:1390 Prepared语句包含太多占位符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！