Mysql: Add support for named input parameters

[karpovicham]

The support for named input parameters has been already added in database/sql with this commit, but go-sql-driver/mysql is not up to date with this feature yet.

Could we please have the support for this feature in go-sql-driver/mysql as well in a near future?

Here is the issue with a full discussion for this topic in database/sql, that may be helpful.
Thanks

[methane]

https://dev.mysql.com/doc/internals/en/com-stmt-prepare.html

Does COM_PREPARE_STMT supports named placeholder?
If no, I think #478 supports named placeholder.

[methane]

reference of prepared statement syntax

[tomponline]

Is there a way of using the SQL package's named param support and converting them back to ordered params in the MySQL package so that the it works with any existing MySQL/MariaDB protocol (as the ordered params work well today)? I.e. emulate it rather than having to prepare a statement. Similar to how I believe PHP's PDO library works.

[methane]

It can be. But it's far from current implementation. We should be careful about it.
I think other Go 1.8 features will be implemented before this.

[tomponline]

Thanks @methane is it somewhat different to the named params implemented in libraries like sqlx?

https://github.com/jmoiron/sqlx

// Named queries can use structs, so if you have an existing struct (i.e. person := &Person{}) that you have populated, you can pass it in as &person
    tx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "[email protected]"})

[methane]

@tomponline I meant I must take time to research a lot, including difference between sqlx and database/sql.

But at least, when DB.Prepare() is called, we can't know NamedValue will be passed to Stmt.Query or not. It can be difference.
For example, what can we do DB.Prepare(SELECT ":na\"me1", :name2, ?)?
We should some difficult preprocess before passing the prepared query to MySQL.
As my forecast, sqlx doesn't care about low level prepared query. But I hadn't read sqlx implementation well.

So we should do much survey, design, discussion, implementation, test about it.

[methane]

My advice is don't wait go-sql-driver/mysql implements it.
Higher level layer like sqlx is better place to implement it.
They can provide more safe and explicit API for named parameter.
You can use driver-independent named parameter format too.

So I recommend you to not use database/sql directly if you want such user-friendly features.

(I'm not against to implement it. But my priority is performance and stability.)

[freman]

It actually works for the most part, I don't know if you guys got to adding support for it but it just sort of works. It's just an edge case we ran into that seems to work in PHP but not go

SELECT * FROM table WHERE type=@type LIMIT @count complains about the @count parameter
SELECT * FROM table WHERE type=@type LIMIT 1 works fine, so thanks for whoever got this much of it working.
SELECT * FROM table WHERE type=@type LIMIT ? works too btw :)

Please can we have @count too?

Edit: maybe it doesn't, might have just been my testing code it worked on (which uses mock)

[julienschmidt]

I'm not sure if and how we should implement this.
There is no server-side support, so we would have to parse the query within the driver, which I would like to avoid as much as possible.

Is anyone interested to research a bit how other MySQL drivers (for other languages) support this? For example in PHP's PDO supports it and maybe also the official Java Connector (I'm not sure if it supports it).
@methane probably knows more about the Python drivers.

[methane]

I don't want to add it.
Since MySQL wire protocol doesn't support named parameter, it means we should emulate it.
So, problem is which is better?

• All drivers has own implementation of named parameter emulation.
• One tool (e.g. sqlx) top on drivers support named parameter.

I strongly prefer later, because:

• It can parse query with named parameter only once, so performance is better.
• User can learn only one dialect and use it for many DBs.
• User can use more friendly helpers (e.g. WHERE id IN :ids).

Is there any reason to add named parameter to this project?
It increases complexity, maintenance cost. User should learn custom dialect and
edge cases only for this driver. It won't make anyone happy.

[mkmik]

• All drivers has own implementation of named parameter emulation.
• One tool (e.g. sqlx) top on drivers support named parameter.

or the database/sql provides emulation for all drivers that don't support it natively;
have you considered bringing this issue up in the go mailing lists?

[methane]

No. I use sqlx.

Adding common emulation layer doesn't solve IN :ids.
And sqlx supports "parse once" without prepared statement. It's impossible (or very difficult) to implement under the interface of database/sql.

[elvizlai]

friendly ping

mysql: driver does not support the use of Named Parameters

[methane]

@elvizlai Please read the thread before firing ping.
Especially this comment.