Set primary key with two integers
I have a table with primary key (ColumnA, ColumnB). I want to make a functio开发者_JS百科n or procedure that when passed two integers will insert a row into the table but make sure the largest integer always goes into ColumnA and the smaller one into ColumnB.
So if we have SetKeysWithTheseNumbers(17, 19) would return
|-----------------|
|ColumnA | ColumnB|
|-----------------|
|19 | 17 |
|-----------------|
SetKeysWithTheseNumbers(19, 17) would return the same thing
|-----------------|
|ColumnA | ColumnB|
|-----------------|
|19 | 17 |
|-----------------|
Use:
INSERT INTO your_table
(COLUMN_A, COLUMN_B)
VALUES(GREATEST(@param_a, @param_b),
LEAST(@param_a, @param_b))
Reference:
- GREATEST
- LEAST
Here is a store procedure
DELIMITER $$
DROP PROCEDURE IF EXISTS `SetKeysWithTheseNumbers` $$
CREATE PROCEDURE `SetKeysWithTheseNumbers` (
n1 integer,
n2 integer
)
BEGIN
declare n3 integer;
declare n4 integer;
if( n1 > n2 ) then
set n3=n1;
set n4=n2;
else
set n3=n2;
set n4=n1;
end if;
insert into mytable values(n3,n4);
END $$
DELIMITER ;
You could try triggers:
delimiter //
CREATE TRIGGER greater_ColA_table
BEFORE INSERT ON table
FOR EACH ROW
BEGIN
DECLARE a INTEGER;
IF NEW.ColumnA < NEW.ColumnB THEN
SET a=NEW.ColumnA;
SET NEW.ColumnA=NEW.ColumnB, NEW.ColumnB=a;
END IF;
END //
delimiter ;
Repeat for the BEFORE UPDATE
event.
精彩评论