Mysql

我可以避免加入我的選擇程序嗎?

  • April 28, 2016

這是我想創建的選擇過程的一個非常簡單的範例,儘管它只完成了部分(它非常不言自明):

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 ;

引用自:https://dba.stackexchange.com/questions/136684