Mysql
我可以避免加入我的選擇程序嗎?
這是我想創建的選擇過程的一個非常簡單的範例,儘管它只完成了部分(它非常不言自明):
delimiter // CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime, _hashid int, _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1)) begin START TRANSACTION; SELECT `first_name`, `last_name`, `hashID` FROM MYDB.person; SELECT`id`, `id_status`, `criminal_status`, `hashID` FROM MYDB.id_crimes; SELECT `stateID`, `stateID_status`, `dob`, `hashID` FROM MYDB.dob_state; SELECT `create_time`, `update_time`, `hashID` FROM MYDB.timestamps; COMMIT; end// delimiter ;
我正在尋找的是絕對最簡潔的方式來選擇一個完整的記錄,並將其作為一個程序。我真的很想避免在表上進行連接,但是鑑於上述限制,我真的看不出有任何方法可以在 hashID 上進行連接。還有其他方法嗎?指針?在程序中會是什麼樣子?我想看看有人對我想做的事情的簡明糾正。:) 謝謝!
您可以使用變數:
delimiter // CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime, _hashid int, _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1)) begin START TRANSACTION; SELECT @fn:=`first_name`, @ln:=`last_name`, @hid:=`hashID` FROM MYDB.person WHERE <condition>; SELECT @id:=`id`, @s:=`id_status`, @cs:=`criminal_status` FROM MYDB.id_crimes WHERE `hashID` = @hid; SELECT @sid:=`stateID`, @sids:=`stateID_status`, @dob:=`dob` FROM MYDB.dob_state WHERE `hashID` = @hid; SELECT @ct:=`create_time`, @ut:=`update_time` FROM MYDB.timestamps WHERE `hashID` = @hid; COMMIT; SELECT @fn AS `first_name`, @ln AS `last_name`, @hid AS `hashID`, @id AS `id`, @s AS `id_status`, @cs AS `criminal_status`, @sid AS `stateID`, @sids AS `stateID_status`, @dob AS `dob`, @ct AS `create_time`, @ut AS `update_time`; end// delimiter ;
編輯:
您還可以使用
SELECT <columns,...> INTO <variables,...>
:您可以使用變數:
delimiter // CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime, _hashid int, _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1)) begin START TRANSACTION; SELECT `first_name`, `last_name`, `hashID` INTO @fn, @ln, @hid FROM MYDB.person WHERE <condition>; SELECT `id`, `id_status`, `criminal_status` INTO @id, @s, @cs FROM MYDB.id_crimes WHERE `hashID` = @hid; SELECT `stateID`, `stateID_status`, `dob` INTO @sid, @sids, @dob FROM MYDB.dob_state WHERE `hashID` = @hid; SELECT `create_time`, `update_time` INTO @ct, @ut FROM MYDB.timestamps WHERE `hashID` = @hid; COMMIT; SELECT @fn AS `first_name`, @ln AS `last_name`, @hid AS `hashID`, @id AS `id`, @s AS `id_status`, @cs AS `criminal_status`, @sid AS `stateID`, @sids AS `stateID_status`, @dob AS `dob`, @ct AS `create_time`, @ut AS `update_time`; end// delimiter ;