Skip to content

Instantly share code, notes, and snippets.

@jamiejackson
Created February 11, 2021 22:24
Show Gist options
  • Save jamiejackson/f41f6f4d0dec9759d5ea303fdb4b4f4e to your computer and use it in GitHub Desktop.
Save jamiejackson/f41f6f4d0dec9759d5ea303fdb4b4f4e to your computer and use it in GitHub Desktop.
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a | bit(1) | YES | | NULL | |
| b | bit(1) | YES | | NULL | |
| c | bit(1) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | | NULL |
| 0 | NULL | 1 |
+------+------+------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | 1 | 1 |
+------+------+------+
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | tinyint(1) | YES | | NULL | |
| b | tinyint(1) | YES | | NULL | |
| c | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | NULL | 1 |
+------+------+------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | 1 | 1 |
+------+------+------+
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a | bit(1) | YES | | NULL | |
| b | bit(1) | YES | | NULL | |
| c | bit(1) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | NULL | 1 |
+------+------+------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | 1 | 1 |
+------+------+------+
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | tinyint(1) | YES | | NULL | |
| b | tinyint(1) | YES | | NULL | |
| c | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | NULL | 1 |
+------+------+------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | 1 | 1 |
+------+------+------+
version: '3.6'
services:
dbexp:
image: ${IMAGE:-mariadb:10.5}
container_name: dbexp
environment:
MYSQL_ALLOW_EMPTY_PASSWORD: 1
MYSQL_ROOT_PASSWORD: ''
ports:
- 3306:3306
volumes:
- ./:/app
create schema if not exists `test`;
use test;
drop table if exists test;
create table test(
a bit,
b bit,
c bit
);
insert into test(a,b,c) values(0, 1, NULL);
show columns from test.test;
# a couple of union examples
select a, b, c from test
union select 0, NULL, 1;
select a, b, c from test
union select 0, 1, 1;
# CONVERT TO TINYINT
alter table test.test modify column a boolean default NULL NULL;
alter table test.test modify column b boolean default NULL NULL;
alter table test.test modify column c boolean default NULL NULL;
show columns from test.test;
# same union examples as above
select a, b, c from test
union select 0, NULL, 1;
select a, b, c from test
union select 0, 1, 1;
$ IMAGE=mariadb:10.2 docker-compose up -d
$ docker exec dbexp bash -c 'mysql --table < /app/script.sql'
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a | bit(1) | YES | | NULL | |
| b | bit(1) | YES | | NULL | |
| c | bit(1) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | | NULL |
| 0 | NULL | 1 |
+------+------+------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | 1 | 1 |
+------+------+------+
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | tinyint(1) | YES | | NULL | |
| b | tinyint(1) | YES | | NULL | |
| c | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | NULL | 1 |
+------+------+------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | 1 | 1 |
+------+------+------+
$
$
$
$ docker-compose down
$ IMAGE=mariadb:10.5 docker-compose up -d
$ docker exec dbexp bash -c 'mysql --table < /app/script.sql'
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a | bit(1) | YES | | NULL | |
| b | bit(1) | YES | | NULL | |
| c | bit(1) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | NULL | 1 |
+------+------+------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | 1 | 1 |
+------+------+------+
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | tinyint(1) | YES | | NULL | |
| b | tinyint(1) | YES | | NULL | |
| c | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | NULL | 1 |
+------+------+------+
+------+------+------+
| a | b | c |
+------+------+------+
| 0 | 1 | NULL |
| 0 | 1 | 1 |
+------+------+------+
$
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment