Using MySQL with Apache
222
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
In this case (when the
AUTO_INCREMENT
column is part of a multiple-column index),
AUTO_INCREMENT
values are reused if you delete the row with the biggest
AUTO_INCREMENT
value in
any group. This happens even for
MyISAM
tables, for which
AUTO_INCREMENT
values normally are not
reused.
If the
AUTO_INCREMENT
column is part of multiple indexes, MySQL will generate sequence values
using the index that begins with the
AUTO_INCREMENT
column, if there is one. For example, if the
animals
table contained indexes
PRIMARY KEY (grp, id)
and
INDEX (id)
, MySQL would
ignore the
PRIMARY KEY
for generating sequence values. As a result, the table would contain a single
sequence, not a sequence per
grp
value.
To start with an
AUTO_INCREMENT
value other than 1, you can set that value with
CREATE TABLE
or
ALTER TABLE
, like this:
mysql>
ALTER TABLE tbl AUTO_INCREMENT = 100;
More information about
AUTO_INCREMENT
is available here:
• How to assign the
AUTO_INCREMENT
attribute to a column:
Section 13.1.10, “
CREATE TABLE
Syntax”
, and
Section 13.1.4, “
ALTER TABLE
Syntax”
.
• How
AUTO_INCREMENT
behaves depending on the
NO_AUTO_VALUE_ON_ZERO
[536]
SQL mode:
Section 5.1.7, “Server SQL Modes”
.
• How to use the
LAST_INSERT_ID()
[961]
function to find the row that contains the most recent
AUTO_INCREMENT
value:
Section 12.13, “Information Functions”
.
• Setting the
AUTO_INCREMENT
value to be used:
Section 5.1.4, “Server System Variables”
.
•
AUTO_INCREMENT
and replication:
Section 16.4.1.1, “Replication and
AUTO_INCREMENT
”
.
• Server-system variables related to
AUTO_INCREMENT
(
auto_increment_increment
[1446]
and
auto_increment_offset
[1449]
) that can be used for replication:
Section 5.1.4, “Server System
Variables”
.
3.7. Using MySQL with Apache
There are programs that let you authenticate your users from a MySQL database and also let you write
your log files into a MySQL table.
You can change the Apache logging format to be easily readable by MySQL by putting the following
into the Apache configuration file:
LogFormat \
"\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \
\"%U\",\"%{Referer}i\",\"%{User-Agent}i\""
To load a log file in that format into MySQL, you can use a statement something like this:
LOAD DATA INFILE '
/local/access_log
' INTO TABLE
tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
The named table should be created to have columns that correspond to those that the
LogFormat
line
writes to the log file.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
