Database Reference
In-Depth Information
+------------+------------------+
| BTH00415JP | lavatory |
| KIT01729JP | microwave oven |
| BED00038SG | bedside lamp |
| KIT00372UK | garbage disposal |
| BTH00485US | shower stall |
| DIN40672US | dining table |
+------------+------------------+
The
ORDER
BY
clauses just shown suffice to sort by substrings of the
id
values, but if such
operations on the table are common, it might be worth representing houseware IDs
differently; for example, using separate columns for the ID components. This table,
housewares2
, is like
housewares
but uses
category
,
serial
, and
country
columns
rather than an
id
column:
CREATE
TABLE
housewares2
(
category
VARCHAR
(
3
)
NOT
NULL
,
serial
INT
(
5
)
UNSIGNED
ZEROFILL
NOT
NULL
,
country
VARCHAR
(
2
)
NOT
NULL
,
description
VARCHAR
(
255
),
PRIMARY
KEY
(
category
,
country
,
serial
)
);
With the ID values split into separate parts, sorting operations are easier to specify; refer
to individual columns directly rather than pulling out substrings of the original
id
column. You can also make operations that sort the
serial
and
country
columns more
efficient by adding indexes on those columns. But a problem remains: how do you
display each product ID as a single string rather than as three separate values? Do that
with
CONCAT()
:
mysql>
SELECT category, serial, country,
->
CONCAT(category,serial,country) AS id
->
FROM housewares2 ORDER BY category, country, serial;
+----------+--------+---------+------------+
| category | serial | country | id |
+----------+--------+---------+------------+
| BED | 00038 | SG | BED00038SG |
| BTH | 00415 | JP | BTH00415JP |
| BTH | 00485 | US | BTH00485US |
| DIN | 40672 | US | DIN40672US |
| KIT | 01729 | JP | KIT01729JP |
| KIT | 00372 | UK | KIT00372UK |
+----------+--------+---------+------------+
This example illustrates an important principle: you might think about values one way
(
id
values as single strings), but you need not necessarily represent them that way in
the database. If an alternative representation (separate columns) is more efficient or
easier to work with, it may well be worth using—even if you must reformat the under‐
lying columns so they appear as people expect.
