Exporting data to a simple CSV file

While databases are a great tool to store and manage your data, you sometimes need to extract some of the data from your database to use it in another tool (a spreadsheet application being the most prominent example for this). In this recipe, we will show you how to utilize the respective MySQL commands for exporting data from a given table into a file that can easily be imported by other programs.

Getting ready

To step through this recipe, you will need a running MySQL database server and a working installation of a SQL client (like MySQL Query Browser or the mysql command line tool). You will also need to identify a suitable export target, which has to meet the following requirements:

• The MySQL server process must have write access to the target file

• The target file must not exist

In addition, a user with FILE privilege is needed (we will use an account named sample_install for the following steps; see also Chapter 8Creating an installation user)

Finally, we need some data to export. Throughout this recipe, we will assume that the data to export is stored in a table named table1 inside the database sample. As export target, we will use the file C:/target.csv (MySQL accepts slashes instead of backslashes in Windows path expressions). This is a file on the machine that runs the MySQL server instance, so in this example MySQL is assumed to be running on a Windows machine. To access the results from the client, you have to have access to the file (for example, using a file share or executing the MySQL client on the same machine as the server).

How to do it...

mysql> SELECT * FROM sample.table1 INTO OUTFILE 'C:/target.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

There's more...
While the previous SELECT … INTO OUTFILE statement will work well in most cases, there are some circumstances in which you still might encounter problems. The following topics will show you how to handle some of those.
Handling errors if the target file already exists
If you try to execute the SELECT … INTO OUTFILE statement twice, an error File 'C:/target.csv' already exists occurs. This is due to a security feature in MySQL that makes sure that you cannot overwrite existing files using the SELECT … INTO OUTFILE statement. This makes perfect sense if you think about the consequences. If this were not the case, you could overwrite the MySQL data files using a simple SELECT because MySQL server needs write access to its data directories. As a result, you have to choose different target files for each export (or remove old files in advance).
Unfortunately, it is not possible to use a non-constant file name (like a variable) in the SELECT … INTO OUTFILE export statement. If you wish to use different file names, for example, with a time stamp as part of the file name, you have to construct the statement inside a variable value before executing it:
              Code View:              Scroll                /                Show All
mysql> SET @selInOutfileCmd := concat("SELECT * FROM sample.table1 INTO OUTFILE 'C:/target-", DATE_FORMAT(now(),'%Y-%m-%d_%H%i%s'), ".csv' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"' LINES TERMINATED BY '\r\n';");
mysql> PREPARE statement FROM @selInOutfileCmd;
mysql> EXECUTE statement;



					  


The first SET statement constructs a string, which contains a SELECT statement. While it is not allowed to use variables for statements directly, you can construct a string that contains a statement and use variables for this. With the next two lines, you prepare a statement from the string and execute it.
Handling NULL values
Without further handling, NULL values in the data you export using the previous statement would show up as"N in the resulting file. This combination is not recognized, for example, by Microsoft Excel, which breaks the file (for typical usage). To prevent this, you need to replace NULL entries by appropriate values. Assuming that the table sample.table1 consists of a numeric column a and a character column b, you should use the following statement:
              Code View:              Scroll                /                Show All
mysql> SELECT IFNULL(a, 0), IFNULL(b, "NULL") FROM sample.table1 INTO OUTFILE 'C:/target.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';



					  


The downside to this approach is that you have to list all fields in which a NULL value might occur.
Handling line breaks
If you try to export values that contain the same character combination used for line termination in the SELECT … INTO OUTFILE statement, MySQL will try to escape the character combination with the characters defined by the ESCAPED BY clause. However, this will not always work the way it is intended. You will typically define \r\n as the line separators. With this constellation, values that contain a simple line break \n will not cause problems, as they are exported without any conversion and can be imported to Microsoft Excel flawlessly. If your values happen to contain a combination of carriage return and line feed, the \r\n characters will be prepended with an escape character ("\r\n), but still the target file cannot be imported correctly. Therefore, you need to convert the full line breaks to simple line breaks:
              Code View:              Scroll                /                Show All
mysql> SELECT a, REPLACE(b, '\r\n', '\n') FROM sample.table1 INTO OUTFILE 'C:/target.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';



					  


With this statement, you will export only line breaks \n, which are typically accepted for import by other programs.
Including headers
For better understanding, you might want to include headers in your target file. You can do so by using a UNION construct:
Code View:              Scroll                /                Show All
mysql> (SELECT 'Column a', 'Column b') UNION ALL (SELECT * FROM sample.table1 INTO OUTFILE 'C:/target.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n');



					  


The resulting file will contain an additional first line with the given headers from the first SELECT clause.

In an email designed to reassure Apple employees who might be a bit trepidatious after the Steve Jobs announcement yesterday afternoon, Apple CEO Tim Cook told workers that "Apple is not going to change." The email, leaked to Ars Technica by a reputable source, includes Cook's reassurance that the company will "continue to make the best products in the world" under his guidance.

The text of the email is included in its entirety below.

Team:

I am looking forward to the amazing opportunity of serving as CEO of the most innovative company in the world. Joining Apple was the best decision I've ever made and it's been the privilege of a lifetime to work for Apple and Steve for over 13 years. I share Steve's optimism for Apple's bright future.

Steve has been an incredible leader and mentor to me, as well as to the entire executive team and our amazing employees. We are really looking forward to Steve's ongoing guidance and inspiration as our Chairman.

I want you to be confident that Apple is not going to change. I cherish and celebrate Apple's unique principles and values. Steve built a company and culture that is unlike any other in the world and we are going to stay true to that-it is in our DNA. We are going to continue to make the best products in the world that delight our customers and make our employees incredibly proud of what they do.

I love Apple and I am looking forward to diving into my new role. All of the incredible support from the Board, the executive team and many of you has been inspiring. I am confident our best years lie ahead of us and that together we will continue to make Apple the magical place that it is.

Tim

Workarounds Issued For 'Apache Killer' Attack

Aug 25, 2011 | 08:36 PM | 0 Comments

The Apache development team late yesterday issued an alert and workarounds in advance of rolling out a patch for the flaw in Apache HTTPD Web Server 1.3 and 2.X. The Apache Killer lets an attacker use a single PC to wage a denial of service attack against an Apache server.

"By sending specially crafted HTTP requests which include malformed range HTTP header, an attacker can disrupt the normal function of the web server, thus disallowing legitimate users to receive responses from the web server," the team's advisory says. "This issue affects all Apache software versions and a patch has not been released yet."

The underlying flaw was apparently first reported on bugtraq in 2007. "It appears due to its lack of sophistication, that it did not get much attention by Apache developers and it has remained unpatched all of this time," wrote Kevin Shortt of the SANS Internet Storm Center, who noted that he had not yet tested the PoC, but planned to.

In an updated advisory posted this morning, the Apache team revealed further exposure of the platform to the attack, and noted that "active use of this tool has been observed."

Meanwhile, vendors were stepping forward today announcing their protections against the Apache Killer attack.

Sourcefire says its IPS and open-source Snort technology have been able to detect this flaw for several years and that its Vulnerability Research Team today beefed up that protection with a new rule specific to the Apache Killer. "A lot of people have been freaking out about the "Apache Killer" tool released on Full-Disclosure last Friday. While it's an effective way to cause a Denial of Service (DoS) against an Apache web server, and readily accessible to your average malfeasant, the good news is you don't need to let your hair catch fire over it, because the VRT had it covered before the tool was even released," blogged Alex Kirk, a member of the Sourcefire VRT.

Trustwave's SpiderLabs earlier this week added protection for the attack to its ModSecurity Web application firewall.

And Imperva subsidiary Incapsula says its Web security service users are shielded from the Apache Killer. "Web sites that are using Incapsula and configured to block illegal resource access attempts are protected from such exploit attempts," according to an Incapsula advisory issued today.

The Apache team promised that a patch or new Apache version for Apache 2.0 and 2.2 would be available this week. "Note that, while popular, Apache 1.3 is deprecated," the advisory says. Meantime, the team offered several workarounds, including limiting the size of the HTTP request field to "a few hundred bytes."

Have a comment on this story? Please click "Add Your Comment" below. If you'd like to contact Dark Reading's editors directly, send us a message.

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Java代码

1. CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
2. day INT(2) UNSIGNED ZEROFILL);
3. INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
4. (2000,2,3),(2000,2,3);
5. 示例表中含有代表用户访问网页的年－月－日值。可以使用以下查询来确定每个月的访问天数：
7. SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
8. GROUP BY year,month;
9. 将返回：
11. +------+-------+------+
12. | year | month | days |
13. +------+-------+------+
14. | 2000 | 01 | 3 |
15. | 2000 | 02 | 2 |
16. +------+-------+------+
17. 该查询计算了在表中按年/月组合的不同天数，可以自动去除重复的询问。

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,3),(2000,2,3);
示例表中含有代表用户访问网页的年－月－日值。可以使用以下查询来确定每个月的访问天数：

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;
将返回：

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+
该查询计算了在表中按年/月组合的不同天数，可以自动去除重复的询问。

You can employ MySQL user variables to remember results without having to store them in temporary variables in the client. (See Section 8.4, “User-Defined Variables”.)

For example, to find the articles with the highest and lowest price you can do this:

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0003 | D      |  1.25 ||    0004 | D      | 19.95 |+---------+--------+-------+

What if you forget the name of a database or table, or what the structure of a given table is (for example, what its columns are called)? MySQL addresses this problem through several statements that provide information about the databases and tables it supports.

You have previously seen SHOW DATABASES, which lists the databases managed by the server. To find out which database is currently selected, use the DATABASE() function:

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+

If you have not yet selected any database, the result is NULL.

To find out what tables the default database contains (for example, when you are not sure about the name of a table), use this command:

mysql> SHOW TABLES;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+

The name of the column in the output produced by this statement is always Tables_in_db_name, where db_name is the name of the database. See Section 12.4.5.38, “SHOW TABLES Syntax”, for more information.

If you want to find out about the structure of a table, the DESCRIBE statement is useful; it displays information about each of a table's columns:

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

Field indicates the column name, Type is the data type for the column, NULL indicates whether the column can contain NULL values, Key indicates whether the column is indexed, and Default specifies the column's default value. Extra displays special information about columns: If a column was created with the AUTO_INCREMENT option, the value will be auto_increment rather than empty.

DESC is a short form of DESCRIBE. See Section 12.8.1, “DESCRIBE Syntax”, for more information.

You can obtain the CREATE TABLE statement necessary to create an existing table using the SHOW CREATE TABLE statement. See Section 12.4.5.12, “SHOW CREATE TABLE Syntax”.

The pet table keeps track of which pets you have. If you want to record other information about them, such as events in their lives like visits to the vet or when litters are born, you need another table. What should this table look like? It needs to contain the following information:

Given these considerations, the CREATE TABLE statement for the event table might look like this:

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));

As with the pet table, it is easiest to load the initial records by creating a tab-delimited text file containing the following information.

Load the records like this:

mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;

Based on what you have learned from the queries that you have run on the pet table, you should be able to perform retrievals on the records in the event table; the principles are the same. But when is the event table by itself insufficient to answer questions you might ask?

Suppose that you want to find out the ages at which each pet had its litters. We saw earlier how to calculate ages from two dates. The litter date of the mother is in the event table, but to calculate her age on that date you need her birth date, which is stored in the pet table. This means the query requires both tables:

mysql> SELECT pet.name,
    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
    -> remark
    -> FROM pet INNER JOIN event
    ->   ON pet.name = event.name
    -> WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

There are several things to note about this query:

You need not have two different tables to perform a join. Sometimes it is useful to join a table to itself, if you want to compare records in a table to other records in that same table. For example, to find breeding pairs among your pets, you can join the pet table with itself to produce candidate pairs of males and females of like species:

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
    -> FROM pet AS p1 INNER JOIN pet AS p2
    ->   ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
+--------+------+--------+------+---------+
| name   | sex  | name   | sex  | species |
+--------+------+--------+------+---------+
| Fluffy | f    | Claws  | m    | cat     |
| Buffy  | f    | Fang   | m    | dog     |
| Buffy  | f    | Bowser | m    | dog     |
+--------+------+--------+------+---------+

In this query, we specify aliases for the table name to refer to the columns and keep straight which instance of the table each column reference is associated with.

Databases are often used to answer the question, “How often does a certain type of data occur in a table?” For example, you might want to know how many pets you have, or how many pets each owner has, or you might want to perform various kinds of census operations on your animals.

Counting the total number of animals you have is the same question as “How many rows are in the pet table?” because there is one record per pet. COUNT(*) counts the number of rows, so the query to count your animals looks like this:

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

Earlier, you retrieved the names of the people who owned pets. You can use COUNT() if you want to find out how many pets each owner has:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

The preceding query uses GROUP BY to group all records for each owner. The use of COUNT() in conjunction with GROUP BY is useful for characterizing your data under various groupings. The following examples show different ways to perform animal census operations.

Number of animals per species:

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+

Number of animals per sex:

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+

(In this output, NULL indicates that the sex is unknown.)

Number of animals per combination of species and sex:

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | NULL |        1 |
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

You need not retrieve an entire table when you use COUNT(). For example, the previous query, when performed just on dogs and cats, looks like this:

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = 'dog' OR species = 'cat'
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+

Or, if you wanted the number of animals per sex only for animals whose sex is known:

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE sex IS NOT NULL
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

If you name columns to select in addition to the COUNT() value, a GROUP BY clause should be present that names those same columns. Otherwise, the following occurs:

See also Section 11.16.3, “GROUP BY and HAVING with Hidden Columns”.

MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions similar to those used by Unix utilities such as vi, grep, and sed.

SQL pattern matching enables you to use “_” to match any single character and “%” to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. You do not use = or <> when you use SQL patterns; use the LIKE or NOT LIKE comparison operators instead.

To find names beginning with “b”:

mysql> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

To find names ending with “fy”:

mysql> SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

To find names containing a “w”:

mysql> SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

To find names containing exactly five characters, use five instances of the “_” pattern character:

mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

The following list describes some characteristics of extended regular expressions:

To demonstrate how extended regular expressions work, the LIKE queries shown previously are rewritten here to use REGEXP.

To find names beginning with “b”, use “^” to match the beginning of the name:

mysql> SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

If you really want to force a REGEXP comparison to be case sensitive, use the BINARY keyword to make one of the strings a binary string. This query matches only lowercase “b” at the beginning of a name:

mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

To find names ending with “fy”, use “$” to match the end of the name:

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

To find names containing a “w”, use this query:

mysql> SELECT * FROM pet WHERE name REGEXP 'w';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

Because a regular expression pattern matches if it occurs anywhere in the value, it is not necessary in the previous query to put a wildcard on either side of the pattern to get it to match the entire value like it would be if you used an SQL pattern.

To find names containing exactly five characters, use “^” and “$” to match the beginning and end of the name, and five instances of “.” in between:

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

You could also write the previous query using the {n} (“repeat-n-times”) operator:

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

Section 11.5.2, “Regular Expressions”, provides more information about the syntax for regular expressions.