MySQL で bit(1) を使う

次のページを参考にしました。
[MySQLに真偽値を格納する場合はbit(1)型のフィールドが最適](https://qiita.com/ka215/items/379c4d46d0c04b7fdb46)

テーブルの作成

```sql:create_table.sql
drop table if exists table_a;
create table table_a (id int primary key, status bit(1), message varchar(20));
quit
```

データの挿入

```sql:insert_data.sql
insert into table_a set id=10,status=true,message='Morning';
insert into table_a set id=20,status=false,message='Afternoon';
insert into table_a set id=30,status=true,message='Evening';
insert into table_a set id=40,status=false,message='Night';
;
select id,HEX(status),message from table_a;
quit
```

実行結果

```text
$ mysql -uscott -ptiger123 test_db < create_table.sql
$ mysql -uscott -ptiger123 test_db < insert_data.sql
id	HEX(status)	message
10	1	Morning
20	0	Afternoon
30	1	Evening
40	0	Night
```

Python3 でデータを読む

```py:data_read.py
#! /usr/bin/python3
# -*- coding: utf-8 -*-
#
#	data_read.py
#					Jan/30/2021
#
import sys
#
import mysql.connector
#
#
# ----------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
#
host_aa='localhost'
user_aa ='scott'
password_aa = 'tiger123'
data_base = 'test_db'
conn = mysql.connector.connect(user=user_aa, password=password_aa, \
	host=host_aa,database=data_base)
#
cursor = conn.cursor(dictionary=True)
#
sql_str="select id, status, message from table_a order by id"
cursor.execute(sql_str)
rows = cursor.fetchall()
for row in rows:
#	print(row)
	print(row["id"],row["status"],row["message"])
#
cursor.close()
conn.close()
#
sys.stderr.write("*** 終了 ***\n")
#
# ----------------------------------------------------------------
```

実行結果

```text
$ ./data_read.py 
*** 開始 ***
10 1 Morning
20 0 Afternoon
30 1 Evening
40 0 Night
*** 終了 ***
```

Node.js でデータを読む

```js:data_read.js
#! /usr/bin/node
// ---------------------------------------------------------------
//	data_read.js
//
//					Jan/30/2021
//
// ---------------------------------------------------------------
'use strict'

// ---------------------------------------------------------------
function cast01 (field, useDefaultTypeCasting )
{
	if ( ( field.type === "BIT" ) && ( field.length === 1 ) ) {

	var bytes = field.buffer();

	return( bytes[ 0 ] === 1 );
	}

	return( useDefaultTypeCasting() );
}

// ---------------------------------------------------------------
console.error ("*** 開始 ***")

var mysql = require('mysql')

var connection = mysql.createConnection ({
	host: 'localhost',
	user: 'scott',
	password: 'tiger123',
	typeCast: cast01
	})

connection.query ('Use test_db')

connection.query("select * from table_a", function (err, rows)
	{
  	if (err) throw err
  	console.log (rows.length)

	var dict_aa = new Object ()

	rows.forEach(function(row)
		{
//		console.log(row)
		console.log(row.id,row.status,row.message)
		})


	connection.end()
	console.error ("*** 終了 ***")
	})

// ---------------------------------------------------------------
```

実行結果

```text
$ ./data_read.js 
*** 開始 ***
4
10 true Morning
20 false Afternoon
30 true Evening
40 false Night
*** 終了 ***
```

Go でデータを読む

```go:data_read.go
// ----------------------------------------------------------------
//
//	data_read.go
//
//					Jan/30/2021
//
// ----------------------------------------------------------------
package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	_ "github.com/go-sql-driver/mysql"
)

type Article struct {
	id int
	status []byte
	message string
}

// ----------------------------------------------------------------
func main() {
	fmt.Fprintf (os.Stderr,"*** 開始 ***\n")

	db, err := sql.Open("mysql", "scott:tiger123@/test_db")
	if err != nil {
		log.Fatal(err)
	}

	err = db.Ping()
	if err != nil {
		log.Fatal(err)
	}

sql_str := "select id,status,message from table_a"
rows, err := db.Query(sql_str)
if err != nil {
        fmt.Println(err)
}
defer rows.Close()

for rows.Next() {
	var ar Article
	if err := rows.Scan(&ar.id,&ar.status,&ar.message); err != nil {
                fmt.Println(err)
        }
        fmt.Fprintf (os.Stderr,"%d %b %s \n",ar.id,ar.status,ar.message)
	}


if err := rows.Err(); err != nil {
        fmt.Println(err)
        }

	fmt.Fprintf (os.Stderr,"*** 終了 ***\n")
}

// ----------------------------------------------------------------
```

実行結果

```text
$ go run data_read.go
*** 開始 ***
10 [1] Morning 
20 [0] Afternoon 
30 [1] Evening 
40 [0] Night 
*** 終了 ***
```

Go gorm でデータを読む

```go:data_read_gorm.go
// ----------------------------------------------------------------
//
//	data_read_gorm.go
//
//				  Feb/01/2021
//
// ----------------------------------------------------------------
package main

import (
	"github.com/jinzhu/gorm"
	"fmt"
	"log"
	"os"
	_ "github.com/go-sql-driver/mysql"
)

type Table_a struct {
	Id int
	Status []byte
	Message string
}

// ----------------------------------------------------------------
func main() {
	fmt.Fprintf (os.Stderr,"*** data_read_gorm.go *** 開始 ***\n")

	db, err := gorm.Open("mysql", "scott:tiger123@/test_db")
	if err != nil {
		log.Fatal(err)
	}
	db.SingularTable(true)

	defer db.Close()

	table_a := []Table_a{}

	db.Find(&table_a)

	llx := len(table_a)
	fmt.Println(llx)

	for it:=0; it< llx; it++{
		fmt.Println(table_a[it])
		}

	fmt.Fprintf (os.Stderr,"*** 終了 ***\n")
}

// ----------------------------------------------------------------
```

実行結果

```text
$ go run data_read_gorm.go 
*** data_read_gorm.go *** 開始 ***
4
{10 [1] Morning}
{20 [0] Afternoon}
{30 [1] Evening}
{40 [0] Night}
*** 終了 ***
```


参考ページ
[Casting Bit Fields To Booleans Using The Node.js MySQL Driver](https://www.bennadel.com/blog/3188-casting-bit-fields-to-booleans-using-the-node-js-mysql-driver.htm)
[GolangのGORMで単数形のテーブルを扱う](https://qiita.com/cobot00/items/02fd9adddf851e72587a)