> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# vsql_boolean extension for MySQL

> The vsql_boolean extension adds a STRICTBOOL column type to MySQL that stores one byte, accepts true/false, yes/no, and on/off, and rejects anything else.

MySQL has no boolean. `BOOL` is an alias for `TINYINT(1)`, which accepts `5`
and `-1` as happily as `1`, and gives them back unchanged. `vsql_boolean` adds
a `STRICTBOOL` column type that stores one byte and holds two values.

|                                   |                                                                       |
| --------------------------------- | --------------------------------------------------------------------- |
| **Maintainer**                    | VillageSQL                                                            |
| **Source and full documentation** | [villagesql/vsql-boolean](https://github.com/villagesql/vsql-boolean) |
| **License**                       | GPL-2.0                                                               |

## Install

`vsql_boolean.veb` is already in the server's `lib/veb/` directory if you
installed VillageSQL with the install script, the Docker image, or a release
tarball. Install it into the server with one statement:

```sql theme={null}
INSTALL EXTENSION vsql_boolean;
```

Confirm it is there:

```sql theme={null}
SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_boolean';
```

```
+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_boolean   |
+----------------+
```

To build it yourself, follow the build instructions in the
[repository](https://github.com/villagesql/vsql-boolean).

## What it adds

### The STRICTBOOL type

A `STRICTBOOL` column takes the strings `'true'`/`'false'`, `'t'`/`'f'`,
`'yes'`/`'no'`, `'on'`/`'off'` and `'1'`/`'0'`, in any letter case. Pass them as
strings: a bare number is refused. It reads back as `true` or `false` whichever
form went in, and it occupies one byte.

```sql theme={null}
CREATE TABLE flags (
    id     INT PRIMARY KEY,
    name   VARCHAR(16),
    active STRICTBOOL
);
```

### Functions

| Function            | Returns | What it does                                          |
| ------------------- | ------- | ----------------------------------------------------- |
| `boolean_to_int(b)` | int     | 1 for true, 0 for false                               |
| `boolean_sum(b)`    | int     | Aggregate: how many rows in the group are true        |
| `boolean_avg(b)`    | real    | Aggregate: the fraction that are true, ignoring NULLs |

## Example

```sql theme={null}
INSERT INTO flags VALUES
    (1, 'alpha', 'true'),
    (2, 'beta',  'no'),
    (3, 'gamma', 'on'),
    (4, 'delta', '0');

SELECT name, active FROM flags;
```

```
+-------+--------+
| name  | active |
+-------+--------+
| alpha | true   |
| beta  | false  |
| gamma | true   |
| delta | false  |
+-------+--------+
```

Four spellings went in, and two values came back out. Count them:

```sql theme={null}
SELECT boolean_sum(active) AS enabled, ROUND(boolean_avg(active), 2) AS fraction
FROM flags;
```

```
+---------+----------+
| enabled | fraction |
+---------+----------+
|       2 |      0.5 |
+---------+----------+
```

<Note>
  Anything outside the accepted spellings is refused at insert time:
  `INSERT INTO flags VALUES (5, 'eps', 'maybe')` fails with
  `ERROR 1366 (HY000): Incorrect STRICTBOOL value: 'maybe' for column 'active'
      at row 1`. So does an unquoted `1`, with
  `ERROR 3219 (HY000): Incorrect STRICTBOOL value: '1' for column 'active' at
      row 1`.
</Note>

## See also

* [Choosing data types](/docs/guides/choosing-data-types) — when a narrower type earns its place
* [Install extensions](/docs/mysql-8.4/dev/install) — how `INSTALL EXTENSION` works and where the server looks for a bundle
* [Available extensions](/docs/mysql-8.4/dev/extensions) — the full catalog
* [villagesql/vsql-boolean](https://github.com/villagesql/vsql-boolean) — source, build instructions, and the known limitations