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", "jane.citzen@example.com"})

[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.