Home

MySQL-protobuf

This repo contains a work in progress version of MySQL that has native support for Protocol Buffers, Google's data interchange format. It was developed by a former Google intern during the summer of 2015 and open sourced around October of the same year.

Motivation

Before doing the implementation, we spent a short amount of time studying the benefits of having native protobuf support in MySQL. Two notable advantages are:

• reduced disk usage, due to the compactness of the protobufs binary representation
• very fast schema changes, providing you only change the protobuf schema. What this actually means is that you can very well group your non-key columns inside a protobuf, store them in MySQL as a protobuf column and if you decide to make some changes this is done without rewriting the table. Of course, the changes have to be backwards compatible with the old protobuf schema.

Usage examples

Creating protobuf columns

CREATE TABLE t1 (i INT, p PROTOBUF "message Person {
  optional int64 id = 1;
  optional string name = 2;

  message PhoneNumber {
    optional string number = 1;
    optional string other = 2;
  }

  message Address {
    optional int32 number = 1;
    optional string street = 2;
    optional int32 zipcode = 3;
  }

  message Info {
    optional PhoneNumber phone = 1;
    optional Address address = 2;
  }

  optional Info info = 3;

}") ENGINE=InnoDB;

Show the protobuf definition

SHOW CREATE TABLE t;
SHOW FULL COLUMS FROM t;

Inserting data

INSERT INTO t VALUES(0, 'id: 0 name: "Alice"');
INSERT INTO t VALUES(1, 'name: "Bob"');
INSERT INTO t VALUES(2, 'id: 2
                         name: "Charlie"
                         info {
                           phone { number: "555-555-555" other: "0800-809-999-09" }
                           address { number: 1901 zipcode: 94043 street: "Rock Street" }
                         }');

Querying data

SELECT * FROM t;
SELECT p FROM t;
SELECT p[name] FROM t;
SELECT p[info] FROM t;
SELECT p[info.phone] FROM t;
SELECT p[name] FROM t WHERE p[info.address.number] = 1901;
SELECT p[id], p[name] FROM t WHERE p[info.address.number = "1901";
SELECT p[id], p[name] FROM t WHERE p[info] is NULL;

Updating fields

UPDATE t SET p = 'id: 10 name: "Alice"' WHERE p[id] = 1;
UPDATE t SET p[name] = "Bobby" WHERE p[name] = "Bob";
UPDATE t SET p[info.address.number] = p[info.address.number] + 1 WHERE p[info.address.number] is not NULL;
UPDATE t SET p[name] = NULL WHERE p[name] = "Alice";    # delete a protobuf field

Change protobuf schema

CREATE TABLE t2(i INT, p PROTOBUF 'message M { optional int64 i = 1; optional string s = 2;}',
INSERT INTO t2 VALUES(0, 'i: 0 s: "some string");
ALTER TABLE t2 ADD COLUMN q PROTOBUF 'message Msg { optional int64 i = 1; }';
UPDATE t2 SET q = p;    # throws error because p and q are not compatible protobufs
ALTER TABLE t2 MODIFY COLUMN q PROTOBUF 'message Msg { optional int64 i = 1; optional string s = 2;}';
UPDATE t2 SET q = p;    # works fine now