Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.6Mb
PDF (A4) - 30.7Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.4Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 200.2Kb
Man Pages (Zip) - 311.7Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Adapting an Existing MySQL Schema for a memcached Application Adapting an Existing MySQL Schema for a memcached Application

Consider these aspects of memcached applications when adapting an existing MySQL schema or application to use the memcached interface:

  • memcached keys cannot contain spaces or newlines, because those characters are used as separators in the ASCII protocol. If you are using lookup values that contain spaces, transform or hash them into values without spaces before using them as keys in calls to add(), set(), get() and so on. Although theoretically those characters are allowed in keys in programs that use the binary protocol, you should always restrict the characters used in keys to ensure compatibility with a broad range of clients.

  • If you have a short numeric primary key column in an InnoDB table, you can use that as the unique lookup key for memcached by converting the integer to a string value. If the memcached server is being used for more than one application, or with more than one InnoDB table, consider modifying the name to make sure it is unique. For example, you might prepend the table name, or the database name and the table name, before the numeric value.


    As of MySQL 5.6.14, the InnoDB memcached plugin supports inserts and reads on mapped InnoDB tables that have an INTEGER defined as the primary key.

  • You cannot use a partitioned table for data queried or stored through the memcached interface.

  • The memcached protocol passes numeric values around as strings. To store numeric values in the underlying InnoDB table, for example to implement counters that can be used in SQL functions such as SUM() or AVG():

    • Use VARCHAR columns with enough characters to hold all the digits of the largest expected number (and additional characters if appropriate for the negative sign, decimal point, or both).

    • In any query that performs arithmetic using the column values, use the CAST() function to convert from string to integer or other numeric type. For example:

      -- Alphabetic entries are returned as zero.
      select cast(c2 as unsigned integer) from demo_test;
      -- Since there could be numeric values of 0, can't disqualify them.
      -- Test the string values to find the ones that are integers, and average only those.
      select avg(cast(c2 as unsigned integer)) from demo_test
        where c2 between '0' and '9999999999';
      -- Views let you hide the complexity of queries. The results are already converted;
      -- no need to repeat conversion functions and WHERE clauses each time.
      create view numbers as select c1 key, cast(c2 as unsigned integer) val
        from demo_test where c2 between '0' and '9999999999';
      select sum(val) from numbers;

      Alphabetic values in the result set are turned into 0 by the call to CAST(). When using functions such as AVG() that depend on the number of rows in the result set, include WHERE clauses to filter out any non-numeric values.

  • If the InnoDB column you use as a key can be longer than 250 bytes, hash it to a value that is less than 250 bytes.

  • To use an existing table with the memcached interface, define an entry for it in the innodb_memcache.containers table. To make that the table the default for all requests relayed through memcached, specify the value default in the name column, then restart the MySQL server to make that change take effect. If you are using multiple tables for different classes of memcached data, set up multiple entries in the innodb_memcache.containers table with name values of your choosing, then issue a memcached request of the form get @@name or set @@name within the application to switch the table used for subsequent requests through the memcached API.

    For an example of using a table other than the predefined test.demo_test table, see Example 14.23, “Specifying the Table and Column Mapping for an InnoDB + memcached Application”. For the required layout and meaning of the columns in such a table, see Section 14.17.7, “Internals of the InnoDB memcached Plugin”.

  • To use multiple MySQL column values with memcached key/value pairs, in the innodb_memcache.containers entry associated with the MySQL table, specify in the value_columns field several column names separated by comma, semicolon, space, or pipe characters; for example, col1,col2,col3 or col1|col2|col3.

    Concatenate the column values into a single string using the pipe character as a separator, before passing that string to memcached add or set calls. The string is unpacked automatically into the various columns. Each get call returns a single string with the column values, also delimited by the pipe separator character. you unpack those values using the appropriate syntax depending on your application language.

Example 14.23 Specifying the Table and Column Mapping for an InnoDB + memcached Application

Here is an example showing how to use your own table for a MySQL application going through the InnoDB memcached plugin for data manipulation.

First, we set up a table to hold some country data: the population, area in metric units, and 'R' or 'L' indicating if people drive on the right or on the left.

use test;

CREATE TABLE `multicol` (
  `country` varchar(128) NOT NULL DEFAULT '',
  `population` varchar(10) DEFAULT NULL,
  `area_sq_km` varchar(9) DEFAULT NULL,
  `drive_side` varchar(1) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` bigint(20) unsigned DEFAULT NULL,
  `c5` int(11) DEFAULT NULL,
  PRIMARY KEY (`country`),

Now we make a descriptor for this table so that the InnoDB memcached plugin knows how to access it:

  • The sample entry in the CONTAINERS table has a name column 'aaa'; we set up another identifier 'bbb'. If we made a single master table for all memcached applications to use, we would make the ID 'default' and skip the @@ requests to switch tables.

  • We specify the test.multicol table. The schema name is stored in one column and the table name is stored in another column.

  • The key column will be our unique country value. That column was specified as the primary key when we created the table above, so we also specify the index name 'PRIMARY' here.

  • Rather than a single column to hold a composite data value, we will divide the data among three table columns, so we specify a comma-separated list of those columns that will be used when storing or retrieving values.

  • And for the flags, expire, and CAS values, we specify corresponding columns based on the settings from the sample table demo.test. These values are typically not significant in applications using the InnoDB memcached plugin, because MySQL keeps the data synchronized and there is no need to worry about data expiring or being stale.

insert into innodb_memcache.containers


Here is a sample Python program showing how we would access this table from a program:

  • No database authorization is needed, since all data manipulation is done through the memcached interface. All we need to know is the port number the memcached daemon is listening to on the local system.

  • We load sample values for a few arbitrary countries. (Area and population figures from Wikipedia.)

  • To make the program use the multicol table, we call the switch_table() function that does a dummy GET or SET request using @@ notation. The name in the request is bbb, which is the value we stored in (In a real application, we would use a more descriptive name. This example just illustrates that you specify a table identifier, not the table name, with the GET @@... request.

  • The utility functions to insert and query the data demonstrate how we might turn a Python data structure into pipe-separated values for sending to MySQL with ADD or SET requests, and unpack the pipe-separated values returned by GET requests. This extra processing is only required when mapping the single memcached value to multiple MySQL table columns.

import sys, os
import memcache

def connect_to_memcached():
  memc = memcache.Client([''], debug=0);
  print "Connected to memcached."
  return memc

def banner(message):
  print "=" * len(message)
  print message
  print "=" * len(message)

country_data = [

def switch_table(memc,table):
  key = "@@" + table
  print "Switching default table to '" + table + "' by issuing GET for '" + key + "'."
  result = memc.get(key)

def insert_country_data(memc):
  banner("Inserting initial data via memcached interface")
  for item in country_data:
    country = item[0]
    population = item[1]
    area = item[2]
    drive_side = item[3]

    key = country
    value = "|".join([population,area,drive_side])
    print "Key = " + key
    print "Value = " + value

    if memc.add(key,value):
      print "Added new key, value pair."
      print "Updating value for existing key."

def query_country_data(memc):
  banner("Retrieving data for all keys (country names)")
  for item in country_data:
    key = item[0]
    result = memc.get(key)
    print "Here is the result retrieved from the database for key " + key + ":"
    print result
    (m_population, m_area, m_drive_side) = result.split("|")
    print "Unpacked population value: " + m_population
    print "Unpacked area value      : " + m_area
    print "Unpacked drive side value: " + m_drive_side

if __name__ == '__main__':

  memc = connect_to_memcached()


Here are some SQL queries to illustrate the state of the MySQL data after the script is run, and show how you could access the same data directly through SQL, or from an application written in any language using the appropriate MySQL Connector or API.

The table descriptor 'bbb' is in place, allowing us to switch to the multicol table by issuing a memcached request GET @bbb:

mysql: use innodb_memcache;
Database changed

mysql: select * from containers;
| name | db_schema | db_table  | key_columns | value_columns                    | flags | cas_column | expire_time_column | unique_idx_name_on_key |
| aaa  | test      | demo_test | c1          | c2                               | c3    | c4         | c5                 | PRIMARY                |
| bbb  | test      | multicol  | country     | population,area_sq_km,drive_side | c3    | c4         | c5                 | PRIMARY                |
2 rows in set (0.01 sec)

After running the script, the data is in the multicol table, available for traditional MySQL queries or DML statements:

mysql: use test;
Database changed

mysql: select * from multicol;
| country | population | area_sq_km | drive_side | c3   | c4   | c5   |
| Canada  | 34820000   | 9984670    | R          |    0 |   11 |    0 |
| China   | 1347350000 | 9640821    | R          |    0 |   20 |    0 |
| Denmark | 5543453    | 43094      | R          |    0 |   16 |    0 |
| India   | 1210193422 | 3287263    | L          |    0 |   19 |    0 |
| Ireland | 6399152    | 84421      | L          |    0 |   13 |    0 |
| Mexico  | 113910608  | 1972550    | R          |    0 |   15 |    0 |
| Norway  | 5002942    | 385252     | R          |    0 |   17 |    0 |
| UAE     | 8264070    | 83600      | R          |    0 |   18 |    0 |
| UK      | 62262000   | 243610     | L          |    0 |   14 |    0 |
| USA     | 314242000  | 9826675    | R          |    0 |   12 |    0 |
10 rows in set (0.00 sec)

mysql: desc multicol;
| Field      | Type                | Null | Key | Default | Extra |
| country    | varchar(128)        | NO   | PRI |         |       |
| population | varchar(10)         | YES  |     | NULL    |       |
| area_sq_km | varchar(9)          | YES  |     | NULL    |       |
| drive_side | varchar(1)          | YES  |     | NULL    |       |
| c3         | int(11)             | YES  |     | NULL    |       |
| c4         | bigint(20) unsigned | YES  |     | NULL    |       |
| c5         | int(11)             | YES  |     | NULL    |       |
7 rows in set (0.01 sec)

Allow sufficient size to hold all necessary digits, decimal points, sign characters, leading zeros, and so on when defining the length for columns that will be treated as numbers. Too-long values in a string column such as a VARCHAR are truncated by removing some characters, which might produce a nonsensical numeric value.

We can produce reports through SQL queries, doing calculations and tests across any columns, not just the country key column. (Because these examples use data from only a few countries, the numbers are for illustration purposes only.) Here, we find the average population of countries where people drive on the right, and the average size of countries whose names start with U:

mysql: select avg(population) from multicol where drive_side = 'R';
| avg(population)   |
| 261304724.7142857 |
1 row in set (0.00 sec)

mysql: select sum(area_sq_km) from multicol where country like 'U%';
| sum(area_sq_km) |
|        10153885 |
1 row in set (0.00 sec)

Because the population and area_sq_km columns store character data rather than strongly typed numeric data, functions such as avg() and sum() work by converting each value to a number first. This approach does not work for operators such as < or >: for example, when comparing character-based values, 9 > 1000, which is not you expect from a clause such as ORDER BY population DESC. For the most accurate type treatment, perform queries against views that cast numeric columns to the appropriate types. This technique lets you issue very simple SELECT * queries from your database applications, while ensuring that all casting, filtering, and ordering is correct. Here, we make a view that can be queried to find the top 3 countries in descending order of population, with the results always reflecting the latest data from the multicol table, and with the population and area figures always treated as numbers:

mysql: create view populous_countries as
    cast(population as unsigned integer) population,
    cast(area_sq_km as unsigned integer) area_sq_km,
    drive_side from multicol
  order by cast(population as unsigned integer) desc
  limit 3;
Query OK, 0 rows affected (0.01 sec)

mysql: select * from populous_countries;
| country | population | area_sq_km | drive_side |
| China   | 1347350000 |    9640821 | R          |
| India   | 1210193422 |    3287263 | L          |
| USA     |  314242000 |    9826675 | R          |
3 rows in set (0.00 sec)

mysql: desc populous_countries;
| Field      | Type                | Null | Key | Default | Extra |
| country    | varchar(128)        | NO   |     |         |       |
| population | bigint(10) unsigned | YES  |     | NULL    |       |
| area_sq_km | int(9) unsigned     | YES  |     | NULL    |       |
| drive_side | varchar(1)          | YES  |     | NULL    |       |
4 rows in set (0.02 sec)

User Comments
Sign Up Login You must be logged in to post a comment.