What to do When Your Data Smiles At You…

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.0.4-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> create table bits (val bit);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into bits (val) VALUES (1),(0),(1),(1),(0);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from bits;
+------+
| val  |
+------+
| ☺    |
|      |
| ☺    |
| ☺    |
|      |
+------+
5 rows in set (0.00 sec)

mysql>

7 comments on “What to do When Your Data Smiles At You…”

1. Antony Curtis on June 23, 2008 at 10:56 pm said:

   IBM ASCII 1 looks like the smiley face…

   Nothing unusual… trust me. ;)

   Reply ↓
2. Arjen Lentz on June 23, 2008 at 11:52 pm said:

   Yea – you just have UTF8 working in your console, plus the MySQL connection and everything else on the same wavelength.

   Reply ↓
3. Roland Bouman on June 24, 2008 at 1:21 am said:

   I think this is due to the encoding used by the windows shell.

   Reply ↓
4. Santo Leto on June 24, 2008 at 6:10 am said:

   Yeah, and…I stood in open-mouthed!

   This is not due to 6.0.4-alpha. For bit fields, please try use the following script:

   select version();

   use test;

   drop table if exists bits;
   create table bits (id int auto_increment primary key, val bit(1));

   insert into bits (id) values (1),(2),(3);
   select * from bits;

   update bits set val=b’1′ where id=1;
   update bits set val=b’0′ where id=2;
   update bits set val=b’1′ where id=3;

   select bin(val) from bits;

   Output:

   mysql> select version();
   +————————-+
   | version() |
   +————————-+
   | 5.1.24-rc-community-log |
   +————————-+
   1 row in set (0.00 sec)

   mysql>
   mysql> use test;
   Database changed
   mysql>
   mysql> drop table if exists bits;
   Query OK, 0 rows affected (0.05 sec)

   mysql> create table bits (id int auto_increment primary key, val bit(1));
   Query OK, 0 rows affected (0.09 sec)

   mysql>
   mysql> insert into bits (id) values (1),(2),(3);
   Query OK, 3 rows affected (0.05 sec)
   Records: 3 Duplicates: 0 Warnings: 0

   mysql> select * from bits;
   +—-+——+
   | id | val |
   +—-+——+
   | 1 | NULL |
   | 2 | NULL |
   | 3 | NULL |
   +—-+——+
   3 rows in set (0.00 sec)

   mysql>
   mysql> update bits set val=b’1′ where id=1;
   Query OK, 1 row affected (0.03 sec)
   Rows matched: 1 Changed: 1 Warnings: 0

   mysql> update bits set val=b’0′ where id=2;
   Query OK, 1 row affected (0.03 sec)
   Rows matched: 1 Changed: 1 Warnings: 0

   mysql> update bits set val=b’1′ where id=3;
   Query OK, 1 row affected (0.03 sec)
   Rows matched: 1 Changed: 1 Warnings: 0

   mysql>
   mysql> select bin(val) from bits;
   +———-+
   | bin(val) |
   +———-+
   | 1 |
   | 0 |
   | 1 |
   +———-+

   Reply ↓
5. Mark Leith on June 24, 2008 at 7:23 am said:

   Yes, it has! :) The smiling only happens on Windows however – I guess it just tries to make you more happy! ;)

   http://bugs.mysql.com/bug.php?id=28422

   http://dev.mysql.com/doc/refman/5.0/en/bit-field-values.html

   Reply ↓
6. CaptTofu on June 24, 2008 at 9:50 am said:

   Yes, I’ve had all manner of oddball characters spew out at me, particularly when developing Federated and I break something in the process.

   Reply ↓
7. Cevarief on June 24, 2008 at 8:15 pm said:

   I’m curious to test it on mysql shipped with opensuse 10.3. It shows diamond for bit 1 and blank for 0. When i paste to this comment text…it’s different.

   mysql> select * from bits;
   +——+
   | val |
   +——+
   |
   |
   | |
   |
   |
   | |
   +——+

   Reply ↓