What to do When Your Data Smiles At You…
Posted by Sheeri Cabral on Jun 23, 2008
I have *never* had this happen to me.
Maybe it’s because it’s MySQL 6.0.4, maybe it’s because it’s on Windows, or perhaps I am just up working too late.
I have seen mojibake before, but usually it is unintelligible. But this? After I post this I am backing away slowly from my computer.
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>
Has your data ever smiled at you?
Category: MySQL, Non-Tech Articles
Tags: bit, mojibake, MySQL
IBM ASCII 1 looks like the smiley face…
Nothing unusual… trust me. ;)
Yea – you just have UTF8 working in your console, plus the MySQL connection and everything else on the same wavelength.
I think this is due to the encoding used by the windows shell.
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 |
+———-+
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
Yes, I’ve had all manner of oddball characters spew out at me, particularly when developing Federated and I break something in the process.
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 |
+——+
| |
| |
| |
| |
+——+