I have crated a table with boolean column. But those values are storing as 0 or 1 based on true or false. Please let me know what setting need to modify to store them as true or false .
create table tf (i int ,b boolean);
insert into tf(i,b) values (1,TRUE),(2,FALSE);
select * from tf;
Hi rjsv,
MemSQL is wire-compatible with MySQL, which means that our types must appear identical when used in tables or queried. This means that booleans are actually an alias for the TINYINT(1) type, and the symbols true and false are aliases for 1 and 0 respectively:
mysql> create table t(b boolean not null);
Query OK, 0 rows affected (0.12 sec)
mysql> describe t;
+-------+------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+------+---------+-------+
| b | tinyint(1) | NO | | NULL | |
+-------+------------+------+------+---------+-------+
1 row in set (0.00 sec)
mysql> select true, false;
+------+-------+
| TRUE | FALSE |
+------+-------+
| 1 | 0 |
+------+-------+
so when you see 0/1 returned under such fields in a query, they’re strictly equivalent to true/false but are significantly less expensive to send over the network versus strings. If you are querying directly using a shell it’s possible to tell the server to render them as strings before sending the results back, such as with using a CASE statement. If you’re developing an application on top of MemSQL, the MySQL drivers we’re familiar with generally offer the ability to directly coerce the fields to the native boolean types of the driver’s language.