MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE bool_test(
  id INT AUTO_INCREMENT PRIMARY KEY,
  input_val varchar(50),
  bool_val enum('0','1')
);
insert into bool_test(id, input_val, bool_val) values (1, '0', 0);
Data truncated for column 'bool_val' at row 1

insert into bool_test(id, input_val, bool_val) values (2, '1', 1);
insert into bool_test(id, input_val, bool_val) values (3, '2', 2);
insert into bool_test(id, input_val, bool_val) values (4, '3', 3);
Data truncated for column 'bool_val' at row 1

insert into bool_test(id, input_val, bool_val) values (5, '-1', -1);
Data truncated for column 'bool_val' at row 1

insert into bool_test(id, input_val, bool_val) values (6, 'null', null);
insert into bool_test(id, input_val, bool_val) values (7, '0x00', 0x00);
Data truncated for column 'bool_val' at row 1

insert into bool_test(id, input_val, bool_val) values (8, '''0''', '0');
insert into bool_test(id, input_val, bool_val) values (9, '''1''', '1');
insert into bool_test(id, input_val, bool_val) values (10, '''2''', '2');
insert into bool_test(id, input_val, bool_val) values (11, '''3''', '3');
Data truncated for column 'bool_val' at row 1

insert into bool_test(id, input_val, bool_val) values (11, '''-1''', '-1');
Data truncated for column 'bool_val' at row 1

insert into bool_test(id, input_val, bool_val) values (12, '''a''', 'a');
Data truncated for column 'bool_val' at row 1

insert into bool_test(id, input_val, bool_val) values (13, '''''', '');
Data truncated for column 'bool_val' at row 1

insert into bool_test(id, input_val, bool_val) values (14, 'true', true);
insert into bool_test(id, input_val, bool_val) values (15, 'false', false);
Data truncated for column 'bool_val' at row 1

select id
, input_val
, coalesce(bool_val, 'NULL') as bool_val
, coalesce(bool_val+0, 'NULL') encoded_as
from bool_test


id
input_val
bool_val
encoded_as


2
1
0
1

3
2
1
2

6
null
NULL
NULL

8
'0'
0
1

9
'1'
1
2

10
'2'
1
2

14
true
0
1


show create table bool_test


Table
Create Table


bool_test
CREATE TABLE `bool_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `input_val` varchar(50) DEFAULT NULL,
  `bool_val` enum('0','1') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci