Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why doesn't MySQLi library natively support named parameters?

Proper MySQLi parameterized query syntax from http://php.net/manual/en/mysqli.quickstart.prepared-statements.php:

$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)");
$stmt->bind_param("i", $id);

But never something like:

$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (:id_value)");
$stmt->bind_param("i", "id_value", $id);

It appears to me that named parameter substitution is a reasonable feature to be implemented at the API level. I am surprised that MySQLi only implemented unnamed parameters in the library.

Is there a valid reason? It doesn't make sense to me, seeing how PDO, DQL, ORM all have adopted named parameters in their queries.

I hope it was not the case of "We were lazy & don't wanna" on the part of MySQLi developers. I believe there must've been a good reason and I am looking for that reason, or a way to seek out that reason. The reason for named parameters not being implemented in MySQLi extensions library.

like image 288
Dennis Avatar asked Sep 13 '16 16:09

Dennis


People also ask

Is MySQLi faster than MySQL?

According to all the Google results for benchmarks linked by ceejayoz it looks like MySQL is at least slightly faster than MySQLi in all the benchmark tests.

Which function is used in MySQLi with prepared statements?

There's also a function to simply free the memory associated with the MySQLi result and prepared statement, respectively: $result->free() and $stmt->free() .


2 Answers

MYSQLi doesn't support named parameters for two main reasons:

  1. It is "intended" (I use this term loosely) to be used with a wrapper and
  2. It's counterpart, PDO, does - and there is no point re-inventing the wheel

To elaborate on point 1: mysqli, despite its many downfalls when compared to PDO, becomes easily comparable with a good wrapper - that is, named parameters (among others) are supported by the wrapper rather than mysqli itself. This is by design for one sole reason:

  1. Mysqli is designed to be a fast and flexible library.

If the developers incorporated many more features into the base library, it becomes, counter intuitively, less flexible and requires longer load/execution times.

Both mysqli and pdo were released with PHP 5 (PDO with version 5.3, I believe) and as such are intended for different uses.

You want faster execution times? use mysqli without a wrapper. You want named parameters? use PDO or build a mysqli wrapper to handle such - but be warned, this will hinder your execution times.

like image 89
Mark Avatar answered Sep 20 '22 21:09

Mark


MySQLi, traditionally, is a very thin wrapper for the MySQL API. It doesn't add anything on its own and for a reason: adding such features as named placeholders will require, if you think of it, a whole leviathan of SQL query parsing. Definitely, it is not a job for a database API. Like it is said in the other answer, API is not a DAL or DBAL; they serve for different purposes.

PDO was a great feat you hardly would see again in the language and Wes Furlong is a genius who undertook the task almost single-handedly. But again, PDO is a different story. It's a database access abstraction layer, and to achieve this goal you need a query parser, like it or not. And as you already have a query parser and one of drivers already supports named placeholders, it would be natural to add it to all supported drivers. As you can see, with MySQLi it is all different.

To put it short, it is not about "laziness"; it is about following the specification.

like image 40
Your Common Sense Avatar answered Sep 19 '22 21:09

Your Common Sense