mySQL Stored Procedure to Set a Calculate field Value-CodePudding

I have a scenario:

I have like 5 Boolean fields in a table, I need to set an option set value based on the number of true values of those 5 Boolean Fields:

Field1 Field2 Field3 Field4 Field5

ENUMField1 values (A,B,C,D,E)

If I have 5 of those Boolean fields as True, I will set the ENUM field value as A, 4 Trues and I set them as B and so on

As I may need to change the logic of those values in the future , I need to set/change them on DB side on save ,rather than on controller.

Thanks

As I'm not familiar with stored procedures I couldn't manage to find a solution.

CodePudding user response：

Maybe generated column is more suitable? It will provide actual value always, rather than stored procedure which must be executed each time when you need in actual values..

DEMO

CREATE TABLE test (
  Field1 BOOLEAN, 
  Field2 BOOLEAN,
  Field3 BOOLEAN,
  Field4 BOOLEAN,
  Field5 BOOLEAN);

INSERT INTO test VALUES
(1,1,1,1,1),
(1,1,0,1,1),
(1,0,1,0,1),
(1,1,0,0,0),
(0,0,0,1,0),
(0,0,0,0,0);

SELECT * FROM test;

Records: 6  Duplicates: 0  Warnings: 0

Field1	Field2	Field3	Field4	Field5
1	1	1	1	1
1	1	0	1	1
1	0	1	0	1
1	1	0	0	0
0	0	0	1	0
0	0	0	0	0

ALTER TABLE test
ADD COLUMN ENUMField1 ENUM('A','B','C','D','E','F')
    GENERATED ALWAYS AS (6-Field1-Field2-Field3-Field4-Field5);

SELECT * FROM test;

Records: 0  Duplicates: 0  Warnings: 0

Field1	Field2	Field3	Field4	Field5	ENUMField1
1	1	1	1	1	A
1	1	0	1	1	B
1	0	1	0	1	C
1	1	0	0	0	D
0	0	0	1	0	E
0	0	0	0	0	F

fiddle

CodePudding user response：

A stored procedure is just putting your SQL on the server rather than calling it from the client. There's nothing special about it, and it isn't a way to write logic to run on the server (there are ways to do this, varies on programming language, but the DB is not recommended as a place to store your application code)

You create a SP with the following syntax. Use your own SQL in the middle.

CREATE PROCEDURE sp_GetMovies(IN name varchar(50))
BEGIN
    SELECT title, description FROM movies WHERE title LIKE name;
END

how you call that varies by your client language, but typically its little different than calling a parameterised SQL statement.

CodePudding user response：

Assuming your enum runs from A-Z then using ascii conversions where dec 65 is A

select * ,
        cast(char(65 abs(col1-1) abs(col2-1) abs(col3-1) abs(col4-1) abs(col5 -1)) as char(1)) enumval
from t;

drop table if exists t;

create table t
(col1 tinyint,col2 tinyint,col3 tinyint,col4 tinyint,col5 tinyint, e enum('a','b','c','d')
);

insert into t (col1,col2,col3,col4,col5) 
values
(1,1,1,1,1),
(1,1,1,1,0);

 ------ ------ ------ ------ ------ ------ --------- 
| col1 | col2 | col3 | col4 | col5 | e    | enumval |
 ------ ------ ------ ------ ------ ------ --------- 
|    1 |    1 |    1 |    1 |    1 | NULL | A       |
|    1 |    1 |    1 |    1 |    0 | NULL | B       |
 ------ ------ ------ ------ ------ ------ ---------

If you have more and more columns then you will need dynamic sql.