lua-resty-mysql
lua-resty-mysql copied to clipboard
openresty
Feature/support prepare statement
do support mysql's prepare statement.
there are two ways for calling:
-- first way
mysql:new
db:connect
db:run
-- another way
mysql:new
db:connect
db:prepare
db:execute
db:set_keepalive
PS: It's not implement completely.
@membphis Thanks for your work on this!
I think the biggest selling point of prepared statements is to avoid parsing the SQL queries on the mysqld side upon every request served by OpenResty. So I think it's more important to make prepared statements survive across the boundary of the request handlers and keep states in the cosocket connection pools. Any plans on that side? Otherwise, we can only reuse the prepared statements in the current request handler which is not very common (unless we are inserting a lot of data rows into the database in a single request, for example).
Just for note this #21 already propose prepared statements survive across the boundary of the request handlers and keep states in the cosocket connection pools, i have plan to continue that efforts but lack of skills and time keep me postpone it, i hope this pull request address it or I'll need make effort when the condition permits 😅
there is two style(under current cosocket APIS area).
-- first way
local ok, err = db:connect(...)
local stmt_id, err = db:prepare(prepare_sql)
local res, err = db:execute(statement_id, ...)
db:set_keepalive(...)
the first way is the most simple style, but it have a problem. if the connection is picked from connection pool, the developer doesn't know if it have ready the prepare_statement which it needs, because of the pool is named by user .. ":" .. database .. ":" .. host .. ":" .. port.
-- second way
-- only record host and db info
local ok, err = db:set_connect_info(...)
-- connect, prepare, excute, keepalive
local res, err = db:run(prepare_sql, ...)
the second way, mainly depend on:
different prepare-statement use different connection pool.
so the connect pool is named by user .. ":" .. database .. ":" .. host .. ":" .. port .. ":" .. prepare_sql, please pay attention on the last part prepare_sql, the connect pools is splited by different pool name.
in fact, db:run mainly contains blow steps:
-- inside db:run
local pool = user .. ":" .. database .. ":" .. host
.. ":" .. port .. ":" .. prepare_sql
db:connect({pool=pool})
if db:get_reused_times() == 0 then
db:prepare(prepare_sql)
end
-- the statement_id is 1 if there only one prepare_sql
res = db:execute(1, ...)
db:set_keepalive()
return res
it'll be more simple if we can store data on special cosocket object.
for example:
local ok, err = sock:set_bound_data(key, value)
local value, err = sock:fetch_bound_data(key)
then we can store k-v like this:
key: prepare_sql value: statement_id
-- inside db:prepare(prepare_sql)
local statement_id = self.sock:fetch_bound_data(prepare_sql)
if statement_id then
return statement_id
end
... -- do prepare as current
self.sock:set_bound_data(prepare_sql, statement_id)
return statement_id
@agentzh how about we take # attach arbitrary data to cosocket instances to ngx_lua_module?
This will definitely get merged at some point since this PR was the result of my request to @membphis :) He's been doing me a favor.
Is there anything preventing this to merge? It would be cool to see it merged soon.
Just for the record, we'll redo this atop a new OpenResty builtin API for better performance. We already use the new way to add preparing statement support for PostgreSQL.
Just for the record, we'll redo this atop a new OpenResty builtin API for better performance. We already use the new way to add preparing statement support for PostgreSQL.
Really look forward the PREPARE statement support for MySQL. : )
