CODEWITHSUNDEEP
mysql
Brightshine
Brightshine

Reputation: 965

How to create key->value mapping in MySQL smartly?

I want to query severity/facility from syslog, and translate then from number to meaningful keywords like this:

select case severity 
when 0 then 'emerg'
when 1 then 'Alert'
when 2 then 'Crit'
when 3 then 'Error'
when 4 then 'Warn'
when 5 then 'Notice'
when 6 then 'Info'
when 7 then 'Debug'
end,   

case facility
when 0 then 'kern'
when 1 then 'user'
...
when 23 then 'local7'
end
from logs.sys_log;

While the range of severity is from 0 to 7, and the range of facility is from 0 to 23. I will get a very long query string. Is there any smarter method to create key->value mapping in MySQL, to shorten the query string ?

Upvotes: 0

Views: 669

Answers (1)

hjpotter92
hjpotter92

Reputation: 80639

Create new tables severity_mapping and facility_mapping with two columns:

  • number
  • value

And store the data 0-emerg etc. to first table and 0-kern to the second. Later, use JOIN clauses in your query.

Upvotes: 3

Related Questions