OurSQL Episode 134: When Binary's Not Binary

This week, we take a lighter topic and talk about the many uses and meanings of "binary". Ear candy is a but about SET GLOBAL SQL_BIN_LOG, and At the Movies is Ariel Weisberg of VoltDB explaining sharding and elastic clustering.

Events
DBA Hangops - an every-other-week hangout for dba's (right now it's mostly MySQL) - The DB hangops sessions for April and May are April 10th, April 24th, May 8th and May 22nd.

Upcoming MySQL tech tours

Call for Papers for MySQL Connect is open! The call closes Friday, April 12th.

Percona Live: MySQL Conference and Expo 2013 is being held at the Santa Clara Convention Center Monday April 22nd - Thursday April 25th.  

Oracle is sponsoring a reception on Monday, April 22nd during Percona Live. It's free, so RSVP today!

LinuxFest Northwest will take place in Bellingham, Washington on Sat Apr 27th and Sun Apr 28th.

Training
SkySQL Trainings
FromDual trainings
Tungsten University trainings
Oracle's MySQL Trainings
Percona Trainings
The many uses and meanings of "binary"
binary distribution
binary installation
binary backup
server/client binary
binary format
binary log
VARCHAR CHARSET BINARY
VARCHAR CHARSET UTF8 BINARY

mysql> CREATE TABLE binary_test (bincharset VARCHAR(50) charset binary, bincollate VARCHAR(50) charset utf8 binary, bin VARCHAR(50) binary) ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table binary_test\G
*************************** 1. row ***************************
      Table: binary_test
Create Table: CREATE TABLE `binary_test` (
 `bincharset` varbinary(50) DEFAULT NULL,
 `bincollate` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `bin` varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

BINARY
VARBINARY
CHAR BINARY
CHAR UTF8 BINARY
binary collation
binary string/non-binary string
difference between binary and non-binary strings

Binary and non-binary string case example, and example of converting to lowercase by changing the character set first:

INSERT INTO binary_test VALUES ('aA','aA','aA');
SELECT LOWER(bincharset), LOWER(bincollate), LOWER(bin) FROM binary_test;

SELECT LOWER(CONVERT(bincharset using latin1)) FROM binary_test;
Comparing a non-binary string 'a' to the non-binary string 'a ';
SET NAMES utf8 COLLATE utf8_bin;
SELECT 'a ' = 'a';

vs
SET NAMES binary;
SELECT 'a ' = 'a';
<code>

"binary" operator, i.e. <code>CAST(value AS BINARY)

mysql> SELECT HEX(16);
+---------+
| HEX(16) |
+---------+
| 10      |
+---------+
1 row in set (0.00 sec)
mysql> SELECT OCT(16);
+---------+
| OCT(16) |
+---------+
| 20      |
+---------+
1 row in set (0.00 sec)
mysql> SELECT BIN(16);
+---------+
| BIN(16) |
+---------+
| 10000   |
+---------+
1 row in set (0.00 sec)

SELECT BINARY('r') vs SELECT BIN('r')

BOOLEAN is not a bit string
BIT is a string of binary numbers

mysql> CREATE TABLE bitbool (bool1 BOOLEAN, bool2 TINYINT(1)) ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW CREATE TABLE bitbool\G
*************************** 1. row ***************************
      Table: bitbool
Create Table: CREATE TABLE `bitbool` (
 `bool1` tinyint(1) DEFAULT NULL,
 `bool2` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> INSERT INTO bitbool (bool1,bool2) VALUES(1,1), (-1,-1), (15,15), (-15,-15);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from bitbool;
+-------+-------+
| bool1 | bool2 |
+-------+-------+
|     1 |     1 |
|    -1 |    -1 |
|    15 |    15 |
|   -15 |   -15 |
+-------+-------+
4 rows in set (0.00 sec)
mysql> SELECT BIN(bool1), bool2 FROM bitbool\G
*************************** 1. row ***************************
BIN(bool1): 1
    bool2: 1
*************************** 2. row ***************************
BIN(bool1): 1111111111111111111111111111111111111111111111111111111111111111
    bool2: -1
*************************** 3. row ***************************
BIN(bool1): 1111
    bool2: 15
*************************** 4. row ***************************
BIN(bool1): 1111111111111111111111111111111111111111111111111111111111110001
    bool2: -15
4 rows in set (0.00 sec)

Baron's blog posts about bit values in MySQL

bit functions in mysql for comparison and bit math

Peter Laursen's blog post about bit

Ear Candy
MySQL bug 67433 filed by Jeremy Cole about SET GLOBAL SQL_BIN_LOG pitfalls.

At the Movies
This week in At the Movies, we present Ariel Weisberg of VoltDB explaining sharding and elastic clustering at the March 2013 Open Database Camp. This is an overview of a non-blocking elastic clustering implementation in an SQL database with serializable isolation.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast

Trackback URL for this post:

http://technocation.org/trackback/1238