Книга mysql руководство администратора

Информация

M

y

S

Q

L

M

y

S

Q

L

A d m i n i s t r a t o r ‘ s

G u i d e

MySQLAB

M y S Q L Press 800 East 96th Street, Indianapolis, Indiana
46240 USA

M

y

S

Q

L

MySQL AB

«» — 2005

32.973.26-018.2.75 681.3.07

«» . .. .. , .. , . .. «» : [email protected],
http://www.williamspublishing.com 115419, , / 783, 03150, , /
152.

MySQL AB. MySQL. . : . . . : «», 2005. 624 . . . . ISBN
5-8459-0805-1 (.) , MySQL AB, , MySQL. . , MySQL, , . , MySQL . ,
API-, , MySQL Web, . Web- , . 32.973.26-018.2.75

. , , , MySQL Press. Authorized translation from the English
language edition published by MySQL Press, Copyright 2004 All
rights reserved. No part of this book may be reproduced or
transmitted in any form or by any means, electronic or mechanical,
including photocopying, recording or by any information storage
retrieval system, without permission from the Publisher. Russian
language edition published by Williams Publishing House according
to the Agreement with R&I Enterprises International, Copyright
2005

ISBN 5-8459-0805-1 (.) ISBN 0-672-32634-5 (.)

«», 2005 MySQL AB, 2004

1. 2. MySQL 3. MySQL 4. 5. MySQL 6. MySQL 7. MySQL 8. MySQL 9.
InnoDB 10. MaxDB . MySQL . 14 14 15 76 202 212 365 402 460 498 523
582 588 612 614

1. 1.1. 1.1.1. , 1.2. MySQL 1.2.1. MySQL 1.2.2. MySQL 1.2.3.
MySQL 1.2.4. MySQL 1.2.5. » 2000 » 1.3. MySQL AB 1.3.1. — , MySQL
AB 1.3.2. 1.4. MySQL 1.4.1. , MySQL AB 1.4.2. MySQL 1.4.3. MySQL
1.4.4. MySQL AB 1.5. MySQL 1.5.1. MySQL 4.0 1.5.2. MySQL 4.1 1.5.3.
MySQL 5.0: 1.6. MySQL ( TODO) 1.6.1. , 4.1 1.6.2. , 5.0 1.6.3. ,
5.1 1.6.4. , 1.6.5. , 1.6.6. , 1.7. MySQL 1.7.1. MySQL 1.7.2. MySQL
IRC 1.8. MySQL 1.8.1. , MySQL 1.8.2. SQL 1.8.3. MySQL ANSI 1.8.4.
SQL MySQL 1.8.5. MySQL SQL 1.8.6. MySQL 1.8.7. MySQL 14 14 15 15 16
18 19 20 22 23 25 26 27 30 31 31 32 32 35 37 37 39 41 41 41 42 43
43 46 47 48 48 56 56 57 57 57 58 61 68 70

2. MySQL 2.1. 2.1.1. , MySQL 2.1.2. MySQL 2.1.3. MySQL 2.1.4.
MD5 GnuPG 2.1.5. 2.2. MySQL 2.2.1. MySQL Windows 2.2.2. MySQL Linux
2.2.3. MySQL Mac OS X 2.2.4. MySQL NetWare 2.2.5. MySQL Unix- 2.3.
MySQL 2.3.1. 2.3.2. configure 2.3.3. 2.3.4. MySQL 2.3.5.
MIT-pthreads 2.3.6. MySQL Windows 2.3.7. MySQL Windows 2.4. 2.4.1.
Windows 2.4.2. Unix 2.4.3. MySQL 2.4.4. MySQL 2.4.5. MySQL 2.5.
MySQL 2.5.1. 4.1 5.0 2.5.2. 4.0 4.1 2.5.3. 3.23 4.0 2.5.4. 3.22
3.23 2.5.5. 3.21 3.22 2.5.6. 3.20 3.21 2.5.7. MySQL Windows 2.5.8.
2.5.9. MySQL 2.6. 2.6.1. Linux 2.6.2. Max OS X 2.6.3. Solaris
2.6.4. BSD 2.6.5. Unix 2.6.6. OS/2 2.6.7. BeOS 2.7. Perl 2.7.1.
Perl Unix

7 76 77 77 79 90 90 93 95 95 107 109 112 114 117 118 121 124 127
131 133 136 136 137 138 144 146 149 153 154 154 159 163 165 166 167
167 168 170 170 178 179 183 187 196 197 197 197

8 2.7.2. ActiveState Perl Windows 2.7.3. Perl DBI/DBD 3. MySQL
3.1. MySQL 3.2. MySQL- 3.3. 3.3.1. 3.3.2. 3.3.3. 3.3.4.

198 199 202 202 203 204 204 206 210 210 212 212 212 213 215 218
219 223 223 232 234 260 265 265 268 270 271 272 272 273 277 281 282
286 289 289 295 301 301 303 306 307 309 310 311 318

4. 4.1. MySQL 4.1.1. 4.1.2. MySQL- mysqld-max 4.1.3. mysqld_safe
4.1.4. mysql.server 4.1.5. mysqldmulti MySQL- 4.2. MySQL 4.2.1.
mysqld 4.2.2. SQL 4.2.3. 4.2.4. 4.3. 4.3.1. , 4.3.2. MySQL 4.3.3.
mysqld, 4.3.4. LOAD DATA LOCAL 4.4. MySQL 4.4.1. 4.4.2. 4.4.3. ,
MySQL 4.4.4. MySQL 4.4.5. , : 4.4.6. , : 4.4.7. 4.4.8. (Access
denied) 4.4.9 MySQL 4.1 4.5. MySQL 4.5.1. MySQL 4.5.2. MySQL 4.5.3.
MySQL 4.5.4. 4.5.5. 4.5.6. 4.5.7. 4.6.

4.6.1. 4.6.2. 4.6.3. 4.6.4. 4.7. MySQL 4.7.1. 4.7.2. 4.7.3.
4.7.4. 4.7.5. 4.7.6. 4.7.7. 4.8. MySQL 4.8.1. 4.8.2. 4.8.3. 4.8.4.
4.8.5. 4.8.6. 4.9. MySQL 4.9.1. Windows 4.9.2. Unix 4.9.3. 4.10.
MySQL 4.10.1. 4.10.2. SELECT 4.10.3. 4.10.4. 5. MySQL 5.1. 5.2.
5.3. 5.3.1. 5.3.2. / 5.3.3. SQL 5.3.4. 5.4. 5.5. MySQL 5.6. 5.6.1.
4.0 4.1 5.6.2. 5.0 5.7. 5.8. 5.9. 5.10. 5.11.

9 318 320 333 334 340 340 341 342 343 344 344 344 345 346 346
347 348 351 351 352 354 358 359 360 361 362 362 363 365 365 366 367
369 369 371 371 373 378 378 379 379 380 384 393 399 400

10

402 402 403 403 405 405 406 407 408 415 416 416 418 419 419 420
421 423 424 424 426 427 427 430 430 433 435 435 435 436 437 438 441
446 446 447 448 448 449 451 453 454 455 456 460 460

6. MySQL 6.1. 6.1.1. MySQL 6.1.2. 6.1.3. MySQL 6.1.4. MySQL
6.1.5. 6.2. SELECT 6.2.1. EXPLAIN ( SELECT) 6.2.2. 6.2.3. SELECT
6.2.4. MySQL WHERE 6.2.5. MySQL OR 6.2.6. MySQL IS NULL 6.2.7.
MySQL DISTINCT 6.2.8. MySQL LEFT JOIN RIGHT JOIN 6.2.9. MySQL ORDER
BY 6.2.10. MySQL LIMIT 6.2.11. 6.2.12. INSERT 6.2.13. UPDATE
6.2.14. DELETE 6.2.15. 6.3. 6.3.1. 6.3.2. 6.4. 6.4.1. 6.4.2. 6.4.3.
6.4.4. 6.4.5. MySQL 6.4.6. MylSAM 6.4.7. MySQL 6.4.8. MySQL 6.4.9.
6.5. MySQL 6.5.1. 6.5.2. 6.5.3. MySQL 6.5.4. MySQL 6.5.5. MySQL DNS
6.6. 6.6.1. 7. MySQL 7.1.

7.2. myisampack — MySQL, 7.3. mysql 7.3.1. mysql 7.3.2. SQL-
7.3.3. mysql 7.4. MySQL- mysqladmin 7.5. mysqlbinlog 7.6. MySQL —
mysqlcc 7.7. mysqlcheck 7.8. mysqldump 7.9. mysqhotcopy 7.10.
mysqlimport 7.11. , mysqlshow 7.12. 7.13. replace 8. MySQL 8.1. My
IS AM 8.1.1. MylSAM 8.1.2. , 8.1.3. MylSAM 8.1.4. , MylSAM 8.2.
MERGE 8.2.1. , MERGE 8.3. MEMORY (HEAP) 8.4. BDB (BerkleyDB) 8.4.1.
, BDB 8.4.2. BDB 8.4.3. BDB 8.4.4. BDB 8.4.5. BDB 8.4.6. BDB 8.4.7.
, BDB 8.5. ISAM 9. InnoDB 9.1. InnoDB 9.2. , InnoDB 9.3. InnoDB
MySQL 3.23 9.4. InnoDB 9.5. InnoDB 9.6. InnoDB 9.6.1. , InnoDB 9.7.
InnoDB 9.7.1. InnoDB API- 9.7.2. MylSAM InnoDB 9.7.3. AUTOJNCREMENT
InnoDB 9.7.4. (FOREIGN KEY)

11

461 467 471 475 476 477 480 483 485 487 492 493 495 497 497 498
500 502 504 504 507 509 511 513 515 516 516 517 517 520 520 520 521
523 523 524 524 525 530 534 535 535 536 537 538 539

12

9.7.5. InnoDB MySQL 9.7.6. 9.8. InnoDB 9.9. InnoDB 9.9.1. 9.9.2.
9.10. InnoDB 9.11. InnoDB 9.11.1. InnoDB AUTOCOMMIT 9.11.2. InnoDB
(TRANSACTION ISOLATION LEVEL) 9.11.3. 9.11.4. : SELECT… FOR
UPDATE SELECT… LOCK IN SHARE MODE 9.11.5. : » » 9.11.6. InnoDB
9.11.7. , SQL- InnoDB 9.11.8. MySQL 9.11.9. 9.11.10. 9.12. InnoDB
9.12.1. SHOW INNODB STATUS InnoDB 9.13. 9.14. 9.14.1. 9.14.2.
9.14.3. 9.14.4. 9.15. — 9.15.1. — 9.15.2. 9.15.3. 9.15.4. 9.16.
9.16.1. InnoDB 9.16.2. 9.17. InnoDB 9.18. InnoDB 9.18.1. InnoDB 543
544 546 547 548 550 550 551 551 551 553 554 555 556 557 558 559 559
561 563 568 568 569 569 570 570 571 571 572 572 573 574 574 575 577
579 580 582 582 582 583 583 583

10. MaxDB 10.1. MaxDB 10.2. 10.3. , MaxDB 10.4. MaxDB 10.5.
MaxDB MySQL

10.6. MaxDB MySQL 10.7. MaxDB . MySQL . .2. MySQL .2.1. .2.2. []
MySQL .2.3. .2.4. .2.5. » .2.6. .2.7. .2.8. MySQL .2.9. .2.10.
.2.11. .2.12. / .2.13. .2.14. .2.15. » .2.16. .2.17. .. , .3.1.
MySQL ..2. MySQL .3.3. .4. , .4.1. root .4.2. , MySQL .4.3. MySQL
.4.4. MySQL .4.5. MySQL /tmp/mysql.sock .4.6. .

13 584 584 588 588 590 590 590 592 593 593 594 594 594 595 597
598 598 599 599 599 600 600 601 601 603 603 604 604 606 609 609 610
611 612 614

MySQL (http://www.mysql.com), , MySQL. , , InnoDB. (Michael
«Monty» Widenius) (David Axmark), MySQL. (Paul DuBois), (Stefan
Hinz) (Arjen Lentz) .

, , . , , . , . . , Web- . , , , , . , , . . : E-mail: WWW: : :
[email protected] http: //www.williamspublishing.com
115419, , / 783 03150, , / 152

:

1 MySQL , , SQL (Structured Query Language — ). MySQL , , .
MySQL — , MySQL AB. MySQL (Dual License). (Open Source/Free
Software) GNU General Public License, MySQL AB. 1.4. MySQL Web-
MySQL (http: //www. mysql. com/).

1.1. MySQL (MySQL Referense Guide), . , , , . , «MySQL. »
(«MySQL Language Reference»), SQL, MySQL. , , SQL. MySQL 5.0.1,
MySQL ( 3.23 4.0), . , SQL . . MySQL , , . Internet
http://dev.mysql.com/doc/ , HTML, PDF Windows CHM.

16

1.

, , , , , [email protected] com. (David Axmark) (Michael «Monty»
Widenius). MySQL (MySQL Documentation Team), (Arjen Lentz), (Paul
DuBois) (Stefan Hinz). (2004 ) MySQL AB (. 1.4.2).

1.1.1. , : , SQL, , , Perl, , URL-. : » , mysqladmin, help». . ,
. ‘1 , : » %'». , , . , . , — , . , shell> , , a mysql> ,
mysql. shell> mysql> mysql «shell» . UNIX , sh csh. Windows
command.exe cmd.exe, .

1.1.

17

, , . . , , . , . : _ _, _. , — : mysql> SELECT _ F O _._; RM
, , , , : mysql> SELECT author_name F O biblio_db.author_list;
RM SQL , , . . (‘ [‘ ‘ ] ‘ ) . IF EXISTS : D O TABLE [IF EXISTS] _;
RP , (‘). , : TRIM [[BOTH | LEADING | TRAILING] [_] F O ]; RM , :
{DESCRIBE | DESC} _ [_ _]; ( . . . ) ; . , INSERT… SELECT
INSERT SELECT. , . _, , , :RESET _ [, _] …

Bourne. , , , : s h e l l > _= csh tcsh, -. : s h e l l >
setenv _ s h e l l >

18

1.

1.2. MySQL, MySQL, () , MySQL AB. MySQL AB — , MySQL, , MySQL.
MySQL AB 1.3. , MySQL (http://www.mysql.com/) MySQL MySQL AB. MySQL
— . . — , . MySQL — . , , . «SQL» «MySQL» «Structured Query
Language» (» «). SQL — ; ANSI/ISO SQL. SQL 1986 . «SQL-92» , 1992 ,
«SQL: 1999» — , 1999 , «SQL:2003″ — . » SQL» . MySQL — . , . MySQL
Internet, . , , . MySQL GPL (GNU General Public License), , , . GPL
MySQL , MySQL AB (. 1.4.3). MySQL — , . , , . MySQL , . MySQL
http://dev.mysql.com/techresources/crash-me.php. MySQL ,

1.2. MySQL

19

, , . MySQL , . MySQL , Internet. MySQL — . MySQL — , SQL-, , ,
(API-). MySQL , , , . MySQL, . , MySQL. «MySQL» «—» ( «-«), ,
«MySQL» .

1.2.1. MySQL mSQL , (ISAM) . , mSQL , . SQL- , API, mSQL. API- ,
, mSQL, , MySQL. «MySQL» . » » 10 . , , (Monty Widenius), (My).
MySQL — , . MySQL, — (Sakila) — MySQL AB , » «. , (Ambrose
Twebaze), (). , — (SiSwati) — , . — (), , .

20

1.

1.2.2. MySQL MySQL. 1.5. . C++. . . GNU — Automake, Autoconf
Libtool. API- , C++, Eiffel, Java, Perl, PHP, Python, Ruby Tel. . .
. (MylSAM) (-). . , SQL- . , . , . -, . SQL . , — . MySQL , , . . ,
, . , . : / 1, 2, 3, 4 8 ; FLOAT, DOUBLE, CHAR, VARCHAR, TEXT,
BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, ENUM OpenGIS.

. . SELECT W E E HR , :

1.2. MySQL raysql> SELECT CONCAT(first_name, ‘ ‘ , lastjiame)
-> F O c i t i z e n RM -> W E E income/dependents > 10000
A D age > 30; HR N

21

GROUP B ORDER BY. Y (COUNT (), COUNT (DISTINCT . . . ) , AVG(),
STD(), SUM(), MAX(), MIN() GROUP_CONCAT()).

LEFT OUTER JOIN RIGHT OUTER JOIN SQL, ODBC. , SQL. DELETE,
INSERT, REPLACE UPDATE , . , , . MySQL S O HW , . EXPLAIN , . . ,
ABS — . , , — , ‘ (‘ .

( MySQL 3.22). . , , . , . . . , MySQL AB MySQL , 50 . , MySQL
60 000 , 5 . 64 ( , MySQL 4.1.2, 32 ). 1 16 . 1000 (500 , MySQL
4.1.2). CHAR, VARCHAR, BLOB TEXT. MySQL, TCP/IP . Windows- NT (NT,
2000 ) . UNIX UNIX-.

22

1. Connector/ODBC MySQL , ODBC-. , MySQL MS Access. Windows
UNIX. Connector/ODBC . ODBC 2.5, . Connector/JDBC MySQL Java,
JDBC-. Windows UNIX. Connector/JDBC .

. , l a t i n l (ISO8859-1), german, big5, ujis . , », » ».
MySQL 4.1, Unicode. . . ( ). MySQL . . MySQL , , . . MySQL SQL- , .
, mysqlcheck. MySQL myisamchk — MylSAM. MySQL help -? .

1.2.3. MySQL » MySQL?» » MySQL ?». , , . , , , , . . ISAM, , . ,
— MySQL AB, MySQL , 1996 . MySQL

1.2. MySQL

23

, , . , , . MySQL . , » «. , , , , . MySQL 3.23 MySQL 4.O. , , ,
(. 1.8.7). MySQL . , . (-). , , . MySQL 5.x . innoDB ( ). MySQL
3.23, 3.23.49. InnoDB , . B B (-). D Berkley D , B B B MySQL , D ,
B B D , . (-). , . MySQL 4.0 . Connector/ODBC 3.51 ( ). Connector
/ODBC 3.51 ODBC SDK 3.51 . ODBC- . M ISAM (-). y «» M ISAM, y , , ,
.

1.2.4. MySQL MySQL 3.22 4 . MylSAM MySQL 3.23 8

24

1.

(2 6 3 ). , , , MySQL. InnoDB InnoDB , . . ( ), . 64 . . 1.1 , .
1.1. Linux-Intel, 32- Linux-Alpha Solaris 2.5.1 Solaris 2.6 Solaris
2.7 Intel Solaris 2.7 UltraSPARC NetWare NSS 2 , LFS 8 (?) 2 (4 ) 4
( ) 4 512 8

Linux 2.2 M ISAM 2 , y LFS (Large Files Support — ) ext2. Linux
2.4 ReiserFS. Linux 2.4 LFS. — , — , MySQL. LFS Linux (Andreas
Jaeger) «Large File Support in Linux» (» Linux») no
http://www.suse.de/~aj/linux_lfs.html. MySQL M ISAM , y 4 . S O
TABLE STATUS myisamchk -dv _. HW M ISAM 4 ( y ), CREATE TABLE A G R
W _ E G H M X R W . V _ O _L N T A_0 S ALTER TABLE , . M ISAM, . y
, myisampack . ,

1.2. MySQL

25

50% . . myisampack . M ISAM RAID. y MySQL MERGE, M ISAM , .
y

1.2.5. » 2000 91 MySQL » 2000 » (Y2K) . MySQL Unix, 2037 TIME
STAMP. D T AE DATETIME 9999 . MySQL — sql/time.cc; , » 2000 «.
MySQL 3.22 Y A 0 ER 1901 2155 . 1970 2069; , Y A ER 01, MySQL 2001
.

, MySQL D T DATETIME 9999 , AE TIMESTAMP — 2030 . mysql> DROP
TABLE IF EXISTS y2k; Query OK, 0 rows a f f e c t e d ( 0 . 0 1 s e
c ) ( , 0 (0.01 )) mysql> CREATE TABLE y2k (date DATE, ->
date_time DATETIME, -> time_stamp TIMESTAMP) ; Query OK, 0 rows
a f f e c t e d ( 0 . 0 1 s e c ) mysql> -> -> -> ->
-> -> -> -> -> -> -> -> -> -> INSERT
INTO y2k V L E AUS (‘1998-12-31’,’1998-12-31
(‘1999-01-01’,’1999-01-01 (‘1999-09-09’,’1999-09-09
(‘2000-01-01’,’2000-01-01 (‘2000-02-28’,’2000-02-28
(‘2000-02-29’,’2000-02-29 (‘2000-03-01’,’2000-03-01
(‘2000-12-31’,’2000-12-31 (‘2001-01-01’,’2001-01-01
(‘2004-12-31’,’2004-12-31 (‘2005-01-01’,’2005-01-01
(‘2030-01-01’,’2030-01-01 (‘2040-01-01’,’2040-01-01
(‘9999-12-31′,’9999-12-31 23:59:59′,19981231235959),
00:00:00′,19990101000000), 23:59:59′,19990909235959),
00:00:00′,20000101000000), 00:00:00′,20000228000000),
00:00:00′,20000229000000), 00:00:00′,20000301000000),
23:59:59′,20001231235959), 00:00:00′,20010101000000),
23:59:59′,20041231235959), 00:00:00′,20050101000000),
00:00:00′,20300101000000), 00:00:00′,20400101000000),
23:59:59’,99991231235959);

26Query OK, 14 rows a f f e c t e d (0.01 sec) Records: 14 D u p
l i c a t e s : 0 Warnings: 2 (: 14 : 0 : 2) mysql> SELECT *
FROM y2k; I date 1998- 12-31 1999-01-01 1999-09-09 2000- 01-01
2000- 02-28 2000- 02-29 2000- 03-01 2000- 12-31 2001-01-01
2004-12-31 2005-01-01 2030-01-01 2040-01-01 9999-12-31 d a t e time
1998- 12-31 1999- 01-01 1999- 09-09 2000- 01-01 2000- 02-28 2000-
02-29 2000- 03-01 2000- 12-31 2001- 01-01 2004- 12-31 2005- 01-01
2030- 01-01 2040- 01-01 9999-12-31 23:59: 59 00:00: 00 23:59: 59
00:00: 00 00:00: 00 00:00: 00 00:00: 00 23:59: 59 00:00: 00 23:59:
59 00:00: 00 00:00: 00 00:00; 00 23:59: 59 time_stamp
19981231235959 19990101000000 19990909235959 20000101000000
20000228000000 20000229000000 20000301000000 20001231235959
20010101000000 20041231235959 20050101000000 20300101000000
00000000000000 00000000000000

1.

14 rows in set (0.00 sec) (14 (0.00

))

TIME_STAMP , (2040 9999) , TIMESTAMP. , , ( ) 19700101000000
20300101000000 32 . 64- TIMESTAMP ( ) 2106 . MySQL » 2000 «, , . ,
, . , 00 99 «» . , , , . , MySQL » 2000 «, .

1.3. MySQL ABMySQL AB — MySQL. (David Axmark), (Allan Larsson)
(Michael «Monty» Widenius). MySQL . MySQL AB — , .

1.3. MySQL AB

27

Internet , , . MySQL . MySQL AB MySQL, MySQL, (. 1.2). MySQL .
MySQL AB , MySQL : . . . . . . , MySQL AB . , (Open Source). . , .
. — , Internet. — . Web- MySQL (http://www.mysql.com) MySQL MySQL
AB. , «» «aktiebolag» — » «. MySQL AB MySQL, Inc. , MySQL, Inc.
MySQL GmbH MySQL AB, , , .

1.3.1. — , MySQL AB : » , ?». : MySQL AB , , . , MySQL.

28

1.

, . 2001 . — .

1.3.1.1. MySQL AB MySQL. , . . — MySQL. MySQL AB. , . 1.4.
1.3.1.2. MySQL AB MySQL . , , . MySQL — . , , . , MySQL. , . ,
MySQL-. , : . . . . MySQL. , http://www.mysql.com/training/ :
[email protected] , , http://www.mysql.com/certification/.
1.3.1.3. MySQL AB MySQL , MySQL , .

1.3. MySQL AB

29

, , , , , . , . , . , MySQL, , Perl, PHP . , ,
http://www.mysql.com/consulting/ : [email protected] 1.3.1.4.
MySQL GNU (General Public License — GPL). , GPL MySQL . GPL (, , ,
GPL), MySQL AB (https://order.mysql.com/). MySQL MySQL AB, , GPL, .
, MySQL AB . , , 1.4.3. MySQL AB Open Source GPL , MySQL. innoDB,
ACID, , , , . 1.3.1.5. MySQL AB , , , , MySQL . MySQL AB Web-
http://www.mysql.com/. MySQL . , MySQL AB, [email protected] com.
«MySQL» MySQL MySQL AB (. 1.4.4). MySQL .

30

1.

Web- MySQL (http://www.mysql.com) . 2003 16 . , , , . , . 65%
70% .

1.3.2. Web- MySQL (http://www.mysql.com) MySQL MySQL AB. — ,
(http://www.mysql.com/news-and-events/), [email protected] com. MySQL
AB, MySQL. 1.4.1, http://www.mysql.com/support [email protected] com. ,
MySQL, http://www.mysql.com/training/ , [email protected]
1.3.1.2. MySQL http://www.mysql.com/certification/. 1.3.1.2. ,
http://www.mysql.com/consulting/ : [email protected] 1.3.1.3.
Internet https: //order .mysql. com/. , MySQL AB.
http://www.mysql.com/products/pricing.html. , Web-
http://www.mysql.com [email protected] ( ) [email protected] ( ).
1.4.3. , MySQL AB, [email protected] 1.3.1.5. MySQL
http://www.mysql.com/company/trademark.html : [email protected]
1.4.4. MySQL AB, (http://www.mysql.com/company/jobs/)
[email protected] , .

1.4. MySQL

31

. 1.7.1. , , MySQL (. 1.7.1). MySQL, [email protected] (.
1.7.1.3). , , [email protected] com. , ([email protected] com). Web- MySQL
(http://www.mysql.com) [email protected] com. MySQL AB ,
http://www.mysql.com/company/privacy.html. : [email protected] com.
inf [email protected] com.

1.4. MySQL MySQL.

1.4.1. , MySQL AB MySQL AB , MySQL. MySQL AB . , MySQL, , . , ,
, , MySQL, , API- . MySQL , , MySQL, .
http://www.mysql.com/support/. . , , [email protected] . . , , .
MySQL , . , MySQL AB.

32

1.

1.4.2. MySQL MySQL AB MySQL, , (. 1.3). MySQL: 1. MySQL,
mysqlclient, , GNU- readline, GNU General Public License
(http://fsf.org/licenses/). COPYING MySQL . 2. GNU getopt GNU
Lesser General Public License (http://www.fsf.org/licenses/). 3. (
, regexp) Berkley. 4. MySQL, MySQL 3.22,
(http://www.mysql.com/products/mypl.html). . 5. MySQL GPL. : , — .
. , , MySQL AB. [email protected] MySQL 1.4.3 1.4.4 .

1.4.3. MySQL MySQL GNU General Public License (GPL), , , . GPL
http://www.fsf.org/licenses/.
http://www.fsf.org/licenses/gplfaq.html
http://www.gnu.org/philosophy/enforcinggpl.html. GPL , , Free/Libre
and Open Source («FLOSS»), MySQL, , FLOSS GPL. :
http://www.mysql.com/products/licensing/foss-exception.html

1.4. MySQL

33

MySQL GPL, , MySQL AB. https://order.mysql.com/.
http://www.mysql.com/products/licensing.html. MySQL, MySQL 3.22,
(http://www.mysql.com/products/mypl.html). MySQL . , MySQL , GPL
MySQL MySQL AB (. 1.4.4).

1.4.3.1. MySQL GPL «» , GPL-, GPL. , , GPL- . , . . MySQL, GPL,
, GPL. , — , . , MySQL, GPL. , GPL, MySQL, MySQL. , . MySQL , GPL.
MySQL, . MySQL AB — MySQL, (. 1.4.1). GPL , , Free/Libre and Open
Source («FLOSS»), MySQL, , FLOSS GPL. :
http://www.mysql.com/products/licensing/foss-exception.html

34

1.

, MySQL. , , . Web-
http://www.mysql.com/products/licensing.html. ,
http://www.mysql.com/support/. — , , , : [email protected] com.

1.4.3.1. MySQL GPL MySQL GPL GPL. GPL, , FAQ (Free Software
Foundation) http://www.fsf.org/licenses/gpl-faq.html. GPL , ,
Free/Libre and Open Source («FLOSS»), MySQL, , FLOSS GPL. .
http://www.mysql.com/products/licensing/foss-exception.html. GPL.
MySQL GPL. MySQL , MySQL, . GPL » » («mere aggregation»). MySQL, .
Internet-, Web- MySQL. , MySQL, , MySQL. Internet- MySQL, , ,
MySQL, , . MySQL Web-, ( , ). , Web- , MySQL, MySQL . MySQL, MySQL
.

1.4. MySQL

35

MySQL , MySQL AB. MySQL (. 1.4.1). MySQL , MySQL AB MySQL . ,
MySQL , MySQL AB. ( , , , , , .)

1.4.4. MySQL AB MySQL MySQL AB Web-, . , , «MySQL» MySQL AB , (.
http://www.mysql.com/company/trademark.html).

1.4.4.1. MySQL MySQL Priority 2001 . MySQL — , , . MySQL MySQL
AB , .

1.4.4.2. MySQL, , Web- (http://www.mysql.com/press/logos.html)
Web- MySQL AB. , , . , . . , http://www.mysql.com. , , , . , ,
MySQL AB , MySQL.

36

1. , MySQL AB . MySQL AB. Web-, , h t t p : //www.mysql. com.
MySQL GPL , MySQL.

— , , : [email protected] com. 1.4.4.3. MySQL MySQL AB MySQL :
MySQL AB , Web-. MySQL AB, ( ) Web- — . MySQL , . MySQL , ,
MySQL.

1.4.4.4. MySQL AB MySQL AB , MySQL AB. — MySQL. 1.3.1.5.
1.4.4.5. «MySQL» MySQL AB MySQL, , «MySQL» MySQL AB. «MySQL» (), ,
, , MySQL MySQL AB. http://www.mysql.com/corapany/trademark.html.
1.4.4.6. «MySQL» «MySQL» , Internet MySQL AB .

1.5. MySQL

37

1.5. MySQL MySQL, , MySQL 4.0, 4.1, 5.0 5.1. . — MySQL 4.0,
4.0.12, ( 2003 ). , 4.0 . MySQL 3.23 . MySQL MySQL 4.1 MySQL 5.0. ,
MySQL 4.1 MySQL 5.0. MySQL 4.1 -, MySQL 5.0 — -. . R- MySQL 4.0 4.1
4.1 ( My ISAM) 5.0 5.0 5.0 5.1 ( 3.23 InnoDB) 5.1 5.1 5.1

1.5.1. MySQL 4.0 MySQL 4.0 . h t t p : //dev.mysql. com, . MySQL
4.0 Web-. , MySQL 4.0, . MySQL , . .

1.5.1.1. , MySQL 4.0 . MySQL 4.0 , , .

38

1. MySQL 4.0 , INSERT, , ( FULLTEXT), COUNT(DISTINCT). MySQL. .
— MySQL — . InnoDB . InnoDB MySQL. ACID-, INSERT DELETE, . .
FULLTEXT MySQL 4.0 , , . , , MySQL-. , MySQL UNION — SQL. MySQL
Novell Netware, NetWare 6.O. , MySQL, T U C T TABLE ( Oracle). R N
AE , , MySQL 4.0 — l a t i n l d e , , , . . mysqld ( ) . . DELETE
UPDATE. Windows . UNIX M ISAM y ( , ).

1.5. MySQL

39

SQL_CALC_FOUND_ROWS FOUND_ROWS() —

, SELECT LIMIT, . (.
http://dev.mysql.com/doc/mysql/en/News.html).

1.5.1.2. MySQL libmysqld MySQL. , MySQL , . MySQL Internet-, , ,
Internet-, , -, . libmysqld MySQL. , GPL, . MySQL , , .

1.5.2. MySQL 4.1 MySQL 4.0 , Unicode, 4.1, , 5.0. . MySQL ,
MySQL. , , MySQL , . 1.5.2.1. , MySQL 4.1 MySQL 4.1, , . MySQL 4.1
(. 1.6.1). , 4.1, . , , MySQL 5.0 (. 1.6.2). MySQL 4.1 -, :
http://dev.mysql.com/downloads/mysql/4.1.html — , . , , , BitKeeper
MySQL 4.1.

40

1.

MySQL 4.1 — ( ), — ( ) (, ). MySQL 4.1 . . «» — SELECT, . » » (
) — F O . RM . — . BTREE HEAP, . . CREATE TABLE _2 LIKE _1 , , . M
ISAM y OpenGIS . SSL-.

, — , . , , . S O W R I G , H W A NN S .

, , MySQL Unicode utf8nucs2. , . , , Web-.

HELP , SQL. , , . SQL, , . —

1.6. MySQL ( TODO)

41

, help mysql . — . — . , , . INSERT.. .ON DUPLICATE K Y
UPDATE…, E U D T , P AE INSERT , . G O P C N A (), R U_ O CT . (.
http://dev.mysql.com/doc/mysql/en/News.html).

1.5.3. MySQL 5.0: MySQL 5.0, (. 1.6.2). , MySQL, BitKeeper MySQL
5.O. 2003 , 5.0.

1.6. MySQL ( TODO) , MySQL. . , , , . ! , — , ([email protected]),
. — . , , .

1.6.1. , 4.1 MySQL 4.1 , , MySQL 4.1 -. , MySQL 4.1, 1.5.2.1.
OpenSSL. ( SSL MySQL 4.0 100% .) .

42

1. .

1.6.2. , 5.0 MySQL 5.O. , , , MySQL 5.0 alpha, . , , . , . , , ,
. , MySQL 4.1. , MySQL 4.1, 1.5.2.1. , MySQL, BitKeeper MySQL 5.0.
2003 , 5.0. . SQL:2003. . . M ISAM, y RTREE ( MySQL 4.1 RTREE GIS,
). M M R . E OY , V R H R ( 255 AC A ). M ISAM, y . . S O C L M S F
O _ ( mysql HW OU N R M , ) , . . DELETE MylSAM . , .MYD. M M R : E
OY . ( ). , R N M T B E EA E AL , M R E ( ). EG

1.6. MySQL ( TODO)

43

(. http: //dev.mysql. com/doc/mysql/en/News. html).

1.6.3. , 5.1 . (FOREIGN KEY) , InnoDB.

. . . . (. f rm) . . BIT ( BIT TINYINT). . — , . R N M DATABASE.
EA E , : 1. . 2. , RENAME.

3. . . , RAID.

1.6.4. , . , , . Oracle C N E T B PRIOR SELECT O NC Y . SQL ODBC
3.0. S M (DISTINCT). U INSERT SQL_CONCURRENT mysqld
—concurrent-insert , .

44

1. UPDATE, , UPDATE foo SET @a:=a+b, [email protected], [email protected]+c.

, GROUP BY, : SELECT i d , @a:=COUNT(*), SUM(sura_col)/@a F O _
GROUP B id. RM Y IMAGE LOAD DATA INFILE, TIMESTAMP AUTO_INCREMENT.
LOAD DATA INFILE.. .UPDATE, : , , , . , , . , , — , LOAD DATA
INFILE…REPLACE INTO.

LOAD DATA INFILE, : LOAD DATA INFILE ‘_a.txt f INTO TABLE _
TEXT_FIELDS (_1, _2, _) SET _!=CONCAT[_1, _2), _=23 IGNORE _ , ,
.

SET:

ADD_TO_SET{, ) REMOVE_FROM_SET{, )

, mysql , . , . , . , , . S O INFO HW F O __ . RM SELECT a F O
_1 LEFT JOIN _2 USING (a) ; RM , __1. DELETE REPLACE UPDATE ( , ,
). DATETIME, .

1.6. MySQL ( TODO)

45

GNU regexp ( ). , . (DEFAULT). INSERT, , DEFAULT. A Y (), E E Y
() S M (). N VR O E SQL , , FALSE TRUE. (), , : raysql> CREATE
TABLE t l (a DATE); mysql> INSERT INTO t l VALUES (NOW());
mysql> CREATE TABLE t 2 SELECT MAX (a) FROM t l ; mysql> SHOW
COLUMNS FROM t 2 ;

. He , , M ISAM . y INSERT INTO.. .SELECT , . , . ( ). , , , . ,
. . SELECT MIN(column). . . GROUP BY.

long_query_time . myisampack , PACK COMPRESS.

INSERT, DELETE UPDATE , . ALTER TABLE , , .

46

1. DATE DATETIME , , . configure, ( My ISAM) . LIMIT, : LIMIT @,
@b. mysql Web-. LOCK DATABASES ( ). S O STATUS. . HW . . ORDER B
GROUP BY. Y mysqladmin copy _ COPY mysqld. S O HOSTS . HW NULL .
Item_copy_string , — : SELECT COUNT(*)*(id+0) F O _ GROUP B i d RM
Y ALTER TABLE , , INSERT DELAYED. , UPDATE , . MySQL LynxOS.
__;.

/.

.

1.6.5. , get_changed_tables {-, !, 2, . . . ) . , mmap (), .
mmap () . . SET TI M STAMP=; E — . , , 23 ( ). . _=, — , __ . .

1.6. MySQL ( TODO) , .

47

= . = — . . sql_yacc.yy , . , . ( Access 97). MINUS, INTERSECT
FULL O T R JOIN ( UNION UE LEFT | RIGHT O T R JOIN). UE SQLJDPTION
MAX_SELECT_TIME= . . LIMIT, . //. —

mysqld_safe: FSSTND ( Debian), PID- ///.11, — /var/log. ,
«DATADIR» «pidfile» «log», . . L A D T INFILE , zip. OD AA B O ( ).
LB . MIT-pthreads. JOIN . » » . GETLOCK () . , .

1.6.6. , ANSI/ISO SQL. , .

48

1.

1.7. MySQL1.7.1. MySQL MySQL . , , . , , .

1.7.1.1. MySQL , , http://lists.mysql.com. , . MySQL. , , ,
lists.mysql.com, , . , MySQL. , , . List-ID: Delivered-TO:. MySQL.
anounce MySQL . ; MySQL. mysql MySQL. , . , , , . mysql-digest
mysql . , . bugs , MySQL . bugs-digest bugs . internals , MySQL.
MySQL . internals-digest internals .

1.7. MySQL

49

mysqldoc , MySQL: MySQL AB, . mysqldoc-digest mysqldoc .
benchmarks , . ( MySQL), , , , . benchmarks-digest benchmarks .
packagers MySQL. , , MySQL , MySQL . packagers-digest packagers
Java , MySQL Java. JDBC-, MySQL Connector/J. java-digest j ava .
Win32 , MySQL Microsoft, Windows 9x, Me, NT, 2000 . win32-digest
Win32 . myodbc , MySQL ODBC. myodbc-digest myodbc . gui-tools MySQL
, MySQL Administrator MySQL Control Center. gui-tools-digest
gui-tools . plusplus C++ MySQL.

50

1. plusplus-digest plusplus . msql-mysql-modules , MySQL Perl, ,
DBD:mysql. msql-mysql-modules-digest msql-mysql-modules .

MySQL, MySQL AB. MySQL (. 1.4.1). MySQL ( ). MySQL AB .
[email protected] . [email protected] . subscribe
mysql @, . [email protected] . subscribe mysql-de @. .
h t t p : //www. 4t2.com/mysql.
[email protected] . subscribe mysql-br @..
[email protected] . subscribe mysql @..

1.7.1.2. , : MySQL http: / /dev. mysql. com/doc/. , . (http:
//dev. mysql. com/doc/mysql/en/News .html), , . ,
http://bugs.mysql.com/. , , , . http://lists.mysql.com/.
http://www.mysql.com/search/ Web- MySQL AB, .

1.7. MySQL

51

, MySQL. , , MySQL.

1.7.1.3. , , http://bugs.mysql.com/ — . , . , . , , , , . , ( );
, . , , , , , , . ( ) mysqlbug. scripts ( ) bin ( MySQL). mysqlbug
(, Windows), , ( — MySQL). mysqlbug , , — . , . , MySQL , . , mysql
t e s t < ^ Perl-, . , http: //bugs .mysql. com/, MySQL. , , .
MySQL, [email protected] com. , http://bugs.mysql.com/. , mysqlbug .
, , , MySQL. MySQL. , , , , . , , , , . , , :

52

1.

, -, . , , , , . : () MySQL () , MySQL ( ). , 99 100 . : » — —
?» , MySQL , MySQL. -, . MySQL , , . , , MySQL. . , , , . , . , . ,
, . , , , . — , . — , , , , , ( ). , . Connector/ODBC (MyODBC), ,
MyODBC . , , , 80 . mysqlbug —vertical ( G) , (, EXPLAIN SELECT,
). : MySQL, , MySQL 4.0.12. , mysqladmin version. mysqladmin bin
MySQL. , . . Windows, , My Computer ( ) Help () About ( ). Unix-
uname -a.

1.7. MySQL

53

( ). , . MySQL , . , . , , , . mysqld , , . , mysqld mysqld. ,
mysqldump nodata __ _. . .

, SELECT, EXPLAIN SELECT . . . , , , , SELECT. S O CREATE TABLE
^ , HW . , , — . . mysqlbug. mysql. G , 80 :mysql> SHOW
VARIABLES; mysql> SHOW COLUMNS FROM . . . G mysql> EXPLAIN
SELECT . . . G

mysql> FLUSH STATUS; mysql> SELECT . . . ;

SELECT, >mysql> SHOW STATUS;

mysqld, , . . , . , http://bugs.mysql.com/ . , , mysqladmin
variables extended-status processlist, , .

54

1. , ( 10 ), mysqldump R A M . ED E tar, gzip zip FTP-
ftp://ftp.mysql.cora/pub/mysql/upload/. h t t p : //bugs .mysql.
com/. , MySQL , , , , . , , , . . , . , , . , , FTP-
ftp://ftp.mysql.com/pub/mysql/upload. , , , . , , . , , , mysqld,
MySQL. mysqld, mysql, configure . . , Perl , , . , mysqlaccess,
mysqladmin reload , . , mysqlaccess. mysqladmin reload version , .
, . , — , , , , . , , . , . , , . . , , . — ( ), , .

1.7. MySQL

55

, , , . MySQL — , . , , , . «parse error» (» «), . , , MySQL . h
t t p : //dev.mysql.com/doc/ , , MySQL . — [email protected] com . ,
, MySQL, MySQL, , . MySQL . , , , C E K TABLE REPAIR HC TABLE,
myisamchk. Windows, , S O VARIABLES LIKE ‘lower_case_table_names’
HW 1 2. , , . MySQL , . ( .err ). . mysqld , . mysqld, . , MySQL ,
. MySQL . , , . — , , [email protected] , , , — , , — . , , ,
, .

56

1.

1.7.1.4. , , , , , . , , , . , , . , . HTML. , .

1.7.2. MySQL IRC MySQL IRC (Internet Relay Chat — Internet). /,
. freenode (http://www.freenode.net/) #mysql. , MySQL, SQL.
#mysqlphp. , — MySQL + PHP. #mysqlperl. , — MySQL + Perl. #mysql.
MySQL.

EFnet (http://www.efnet.org/) IRC- IRC, X-Chat
(http://www.xchat.org/). X-Chat ( GPL) Unix-, Windows-.

1.8. MySQL , , MySQL ANSI/ISO SQL. MySQL SQL, , . , , MySQL, , .
SQL 1986 . «SQL-92» , 1992 , «SQL: 1999» — , 1999 , «SQL:2003″ — .
» SQL» . , MySQL . , , , MySQL .

1.8. MySQL

57

, SQL, , . SQL -SQL , MySQL . H N L R A DE MySQL 4.O. — , , «24
, 7 «, , Web . MySQL (10-100 , 100 ) . MySQL , — , . MySQL , — . ,
, , MySQL . NDB- , 2004 . XML .

1.8.1. , MySQL ANSI/ISO, . ODBC 0-3.51.

1.8.2. SQL MySQL SQL — . . , SQL MySQL . MySQL , . SQL mysqld
—sql-mode=»modes». MySQL 4.1, sqljnode SET [SESSION|GLOBAL]
sql_mode=’modes’.

1.8.3. MySQL ANSI mysqld ANSI-, ansi.

58

1.

ANSI ( sql-mode ): —transaction-isolation=SERIALIZABLE
sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT/ANSI_QUOTESf
IGNORE_SPACE,ONLY_FULL_GROUP_BY MySQL 4.1 : SET GLOBAL TRANSACTION
ISOLATION LEVEL SERIALIZABLE; SET GLOBAL sql_mode =
‘REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,
IGNORE_SPACE,ONLY_FULL_GROUP_BY’; . 1.8.2. MySQL 4.1.1 sqljnode , :
SET G O A sql_mode=’ansi’; L BL sqljnode , ANSI. : mysql> SET
GLOBAL sql_mode=’ansi’; mysql> SELECT @@global.sqljnode; ->
‘REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,
IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI’;

1.8.4. SQL MySQL MySQL , , , SQL. , , SQL. , MySQL, , /*! . . .
*/. MySQL , SQL-, SQL . : SELECT /*! STRAIGHT_JOIN */ _ F O 1,2 W E
E . . . RM HR ‘!’ , MySQL : CREATE /*132302 TEMPORARY */ TABLE t (a
INT); , MySQL 3.23.02 , T M O A Y . E P RR MySQL . . MySQL MySQL, —
. : MySQL , ( Unix-). , , , , —

1.8. MySQL

59

My ISAM ISAM. , MylSAM, .MYD, .MYI .frm, . , , , ( ). . . ‘V .
SQL- __. __. SQL , (User space). MySQL , : CREATE TABLE r a l p h .
m y _ t a b l e . . .IN my_tablespace. ANALYZE TABLE, CHECK TABLE,
OPTIMIZE TABLE REPAIR TABLE. CREATE DATABASE DROP DATABASE. DO.
EXPLAIN SELECT — . FLUSH RESET. SET. SHOW. LOAD DATA INFILE.
Oracle. RENAME TABLE. REPLACE DELETE + INSERT. CHANGE ^, DROP _,
DROP INDEX, IGNORE RENAME ALTER TABLE. ADD, ALTER, DROP CHANGE
ALTER TABLE. , , INDEX KEY CREATE TABLE. IF EXISTS DROP TABLE. DROP
TABLE. ORDER BY LIMIT UPDATE DELETE. INSERT INTO…SET _ = DELAYED
INSERT REPLACE. LOW_PRIORITY INSERT, REPLACE, DELETE UPDATE. INTO
OUTFILE STRAIGHT_JOIN SELECT. SQL_SMALL_RESULT SELECT.

SQL-.

60

1. GROUP BY. , . ASC DESC GROUP BY. : = : mysql> SELECT
@a:=SUM(total),@b=COUNT(*),@a/@b AS avg -> F O t e s t _ t a b l
e ; RM mysql> SELECT @tl:=(@t2:=l)[email protected]:=4,@tl,@t2,@t3;

. MEDIUMINT, SET, E U BLOB TEXT. NM AUTO_INCREMENT, BINARY,
NULL, UNSIGNED ZEROFILL. . , SQL-, MySQL . , SQL, ODBC. MySQL &
| | » » (AND) & «» (OR), . MySQL | | O , & & R AND.
MySQL SQL- | | . CONCAT (). CONCAT () , | | .

C U T (DISTINCT ), ON . , ( ISO-8859-1 Latin 1). , BINARY
BINARY, , . % M D (). , N % O MOD(N, M). ‘%’ PostgresSQL. =, , , ,
, , AND, O LIKE R F O SELECT, : RM mysql> SELECT coll=l A D
col2=2 F O ^; N RM LASTINSERTIDO AUTO_INCREMENT.

LIKE . REGEXP N T O REGEXP.

1.8. MySQL CONCAT () CHAR () .

61

B I T _ C 0 U N T ( ) , CASE, E L T ( ) , F R O M _ D A Y S ( )
, FORMAT ( ) , I F ( ) , PASSWORD ( ) , ENCRYPT ( ) , MD5 ( ) ,
ENCODE ( ) , DECODE ( ) , PERIOD_ADD ( ) , P E R I O D _ D I F F (
) , TO_DAYS ( ) WEEKDAY().

TRIM() . SQL . G O P B STD(), RU YBIT_OR(), BIT_AND(), BIT_XOR()
GROUP_CONCAT().

, MySQL, , «TODO» http://dev.mysql.com/doc/mysql/en/TODO.html.
«TODO». . 1.6.

1.8.5. MySQL SQL , MySQL ANSI SQL ODBC SQL, MySQL : V R H R AC A
(. 1.8.7). C A V R H R HR AC A, . . REVOKE. 1.8.5.1. MySQL 4.1 . —
SELECT, . ( ) — F O . RM MySQL . 1.8.5.2. SELECT INTO TABLE MySQL
SQL Sybase: SELECT… INTO TABLE… MySQL SQL INSERT INTO…
SELECT…, . INSERT INTO tbl_temp2 (fld_id) SELECT
tbl_templ.fld_order_id F O tbl_templ W E E tbl_templ.fld_order_id
> 100; RM HR SELECT INTO OUTFILE… CREATE TABLE SELECT… 5.0,
MySQL SELECT.. .INTO .

62

1.

1.8.5.3. MySQL ( 3.23 , 4.0) innoDB BDB. innoDB ACID. MySQL (, M
ISAM) y , » «. M ISAM y AUTOCOMMIT=1. . MySQL , , . . , . , . , ,
InnoDB, . MySQL . MySQL M ISAM, y ? 1. , R L B C C M I , OLA K O MT
. , . . , MySQL , , , , — . , MySQL , . 2. . , , , L C TABLE , , OK
, . 3. , . , , . , 100%, » «. Oracle, —

1.8. MySQL

63

, . MySQL, , . , , . , . . , , , . , , , , 3-5 , , . , , MySQL .
L C TABLE, OK , . R A L C L ( ) ED OA , , , . , , , . INSERT
DELAYED, , , . «» , , . , , , , ( , ). MySQL , (dirty read). . , ,
L C TABLES; OK . ROLLBACK, : 1. L C TABLES , . OK 2. , . 3. . 4. U
L C TABLES . NO K , , . , —

64

1. , — . , . . , : . , . , , , , , , . W E E UPDATE. HR , : » «.
, , . , , , , , . , U D T , : P AE UPDATE tablename SET
pay_back=pay_back+125; UPDATE customer SET
customer_date=’current_date’, address=’new address’, phone=’new
phone’, money_owed_to_us=money_owed_to_us-125 WHERE customer_id=id
A D address=’old address’ A D phone=’old phone’; N N , pay_back
money_owed_to_us. L C TABLES / R L B C OK OLA K . , AUTO_INCREMENT
SQL- LAST_INSERT_ID(), API mysql_insert_id (). , , . , innoDB . My
ISAM — : UPDATE _ SET row_flag = 1 W E E id = ID; HR MySQL 1 , rowf
lag 1 . , MySQL : UPDATE _ SET row_flag = 1 W E E id = ID A D
row_flag 1; HR N

1.8. MySQL 1.8.5.4.

65

MySQL 5.O. 5.1. — , — . , , , , , .

1.8.5.5. MySQL 3.23.44 InnoDB , CASCADING, O DELETE O N N
UPDATE. MySQL FOREIGN KEY CREATE TABLE, . , mysqldump ODBC. My
ISAM. : , , — . . , , — . . .

, , . , , . ( .) MySQL . , , , , My ISAM. , My ISAM , INSERT
SELECT, . , : . , ,

66

1. . , . O DELETE, N , MySQL 4.0 DELETE . , O DELETE, N , DELETE
, . , , . , : , , , . , , . (MySQL , , . MySQL 4.1.1 mysqldump ,
.)

, SQL , . SELECT, : SELECT * FROM tl, t2 WHERE tl.id = t2.id;
FOREIGN KEY O DELETE… ODBC- N WHERE.

1.8.5.6. 5.0 MySQL. { , F O SELECT) 4.1. RM , MySQL Web-, . , ,
. , , . (, , ). , MySQL .

1.8. MySQL

67

. . MySQL , , , SQL, . , , .

1.8.5.7.’—‘ ‘ ‘ . MySQL ‘#’. : /* */. MySQL 3.23.3 , », , ( ).
, , SQL-, , !payment! : U D T account SET credit=credit-‘payment! P
AE , , , , -1: UPDATE.account SET credit=credit1 credit1 — SQL, , .
, , : U D T account SET credit=credit P AE ! , ‘—‘ . , MySQL
3.23.3 , credit1. , mysql , ‘ — — ‘ . , MySQL , 3.23.3. SQL- , ,
*’, replace ‘#’: shell> replace » » » #» <
text-file-with-funny-comments.sql | mysql __

: shell> mysql __ < text-file-with-funny-comments.sql , ‘
‘ ‘#’: shell> replace » » » #» text-file-with-funny-comments.sql
: shell> replace » #» » » text-file-with-funny-comments.sql

68

1.

1.8.6. MySQL MySQL , , , , (constraints) MySQL , . , , . , , , ,
. , (. 1.6.4). , MySQL , , , . , .

1.8.6.1. PRIMARY KEY/UNIQUE, , , . , innoDB, MySQL . , MySQL . ,
MySQL IGNORE , (, INSERT IGNORE U D T IGNORE). MySQL P AE . ,
MySQL, mysql_info() API- . MySQL 4.1 S O HWWARNINGS.

, InnoDB. M ISAM y MySQL 5.1.

1.8.6.2. NOT NULL DEFAULT , MySQL . «» , N L , UL N T NULL, , O
MySQL » » , : , , MySQL , .

1.8. MySQL

69

MySQL , , . , , , MySQL 0. NULL , NULL, MySQL 0 — . ( , MySQL
-DONT_USE_DEFAULT_FIELDS.) INSERT , , N T NULL. O MySQL D T
DATETIME ( ‘2000-02-31’ ‘2000-02-00’). AE , SQL. MySQL , , . ( ),
‘0000-00-00’. , , . , , , . — , , . » » , . , MySQL . , MySQL .
MySQL 5.0 , , , , , , .

1.8.6.3. ENUM SET MySQL 4.x ENUM , , . ENUM, 0, . SET . , ‘ ‘ ,
‘ ‘ ‘ ‘ , ‘ , , , ‘ ‘ a, b ‘ .

70

1.

1.8.7. MySQL1.8.7.1. 3.23, MySQL 3.23, , , , , . » » «». , L C
TABLE OK D O TABLE , RP . ( KILL .) MySQL 4.0.12. SELECT {__) F O t
l , t 2 , t 3 . . . , , RM NULL, . MySQL 4.0.11. DELETE F O _ W E E
RM HR -.

1.8.7.2. 4.0, 4.0, , , , , . , » » «». UNION SELECT — ,
(max_length), NULL. MySQL 4.1.1 — UNION. DELETE . MySQL 4.1.

1.8.7.3. MySQL : FOREIGN K Y , E , . REPLACE ( L A D T REPLACE)
O DELETE CASCADE (O D AA N ).

UNION ALL UNION DISTINCT . ALL UNION, . , , , , D O TABLE, RP
.

1.8. MySQL

71

5.0, DROP TABLE , . ( 2 6 3 2 6 4 — 1) , , . MySQL 4.1. FLUSH
TABLES WITH READ L C CREATE TABLE COMMIT, OK .

ANALYZE TABLE BDB , , mysqld. , MySQL : 001207 22:07:56 bdb:
log_flush: LSN past current end-of-log MySQL F O SELECT, RM . , , ,
FROM, . RIGHT JOIN LEFT JOIN RIGHT JOIN , MySQL NULL , LEFT JOIN
RIGHT JOIN. 5.0, FROM. ALTER TABLE BDB, , , . ( .) ANALYZE TABLE,
OPTIMIZE TABLE REPAIR TABLE , INSERT DELAYED. L C TABLE… FLUSH
TABLES… , OK . BDB . BDB, , mysql — rehash. , . . . , , . , , , ,
, . . ( , , !). : CREATE… SELECT INSERT… SELECT, NULL- AUTO
INCREMENT.

72

1. DELETE, , O DELETE CASCADE. N REPLACE. . .SELECT, INSERT
IGNORE. . .SELECT, .

ORDER BY, . , INSERT… SELECT ORDER BY, SELECT ( , , ,
AUTO_INCREMENT), , . , : , , . , OPTIMIZE TABLE ( , MySQL 4.1.1,
OPTIMIZE TABLE, ANALYZE TABLE REPAIR TABLE ). ( ; , InnoDB My ISAM
— , ). (key__buf f e r _ s i z e ). MySQL, .

mysqlbinlog|mysql. — ORDER BY , , . MySQL ORDER BY , . : ( ). ,
, log-bin=__-. , . mysqlbinlog , LOAD DATA INFILE. RENAME , MERGE.
RPAD () , , . .

1.8. MySQL SELECT RPAD(tl.columnl, 50, ‘ ‘) AS f2,
RPAD(t2.column2, 50, ‘ ‘) AS fl F O tablel as t l LEFT JOIN table2
AS t2 O tl.record=t2.joinID RM N O D R B t2.record; RE Y

73

. , . , -, , V R H R AC A. MySQL 4.1. — 255 (CHAR (255)) , .
5.1, . SET C A A T R SET HRCE , . ‘_’, ‘%’ ESCAPE
LIKE…ESCAPE.

DECIMAL, (, +01.00, 1.00, 01.00), G O P B RU Y . MITpthreads.
MIT-pthreads, , . B O «» G O P BY, O D R B LB RU RE Y DISTINCT.
BLOB max_sort_length . max_sort_length 1024 . . , , : SELECT
DISTINCT LEFT {_, 2048) F O _ RM BIGINT D U L ( O BE 64 ). , . :
BIGINT, IF ELT () — BIGINT DOUBLE, — DOUBLE. , 63
(9223372036854775807) , . MySQL 4.0 BIGINT, MySQL 3.23. , BLOB
TEXT, . C A . HR , V R H R . AC A 255 E U SET. NM

74

1. MIN (), () E U SET , NM . mysqld_saf e mysqld mysqld. ,
mysqladmin refresh , stdout stderr . log , mysqld_safe, _. _.log. ,
, mysqladmin refresh. UPDATE . , . , K Y 2, 1: E mysql> UPDATE _
SET KEY=KEY+1,KEY=KEY+1; , . , : mysql> SELECT * F O temp_table,
temp_table AS t 2 ; RM E R R 1137: Can’t reopen table: ‘temp_table
f RO (ERROR 1137: : ‘temp table’) DISTINCT -, «» . ( ), DISTINCT. ,
, DISTINCT. : SELECT DISTINCT mp3id FROM band_downloads WHERE u s e
r i d = 9 ORDER BY id DESC; SELECT DISTINCT band_downloads.mp3id
FROM band_downloads,band_mp3 WHERE band_downloads.userid = 9 AND
band_mp3.id = band_downloads.mp3id ORDER BY band_downloads.id DESC;
MySQL 3.23.x ( id ). , , , O D R BY. RE MySQL , , , , SQL. , ,
MySQL

1.8. MySQL

75

SQL-. , , , MySQL , . , MySQL » » . , , 1.8.6. P O E U E , R CD
R , P O E U E . R CD R M R E EG . ALTER TABLE , MERGE, MERGE-, , ,
. , ALTER TABLE , . MySQL. . . LOCK TABLE WRITE. FLUSH TABLE. MySQL
3.23.2 UPDATE, , WHERE, , , : UPDATE _ SET KEY=KEY+1 WHERE K Y >
100; E : UPDATE _ SET KEY=KEY+1 WHERE KEY+0 > 100; , MySQL
WHERE.

MySQL 3.23 . , , . .

2 MySQL

, MySQL. 1. , . , MySQL. MySQL , (. 2.1.1). 2. , . MySQL . , ( )
, . — . , . , , , 2.1.2. 3. , . , MySQL, 2.1.3. , 2.1.4. 4. . MySQL
, 2.2. MySQL 2.3. MySQL , , 2.5, , . , 2.6 . 5. . MySQL 2.4, , ,
MySQL . , MySQL, , . , . 6. , MySQL, Perl (. 2.7).

2.1.

77

2.1. MySQL : 1. , MySQL . 2. . 3. . , . , , , .

2.1.1. , MySQL , MySQL. GNU Autoconf, MySQL , C++ POSIX. ( .
C++.) Linux (SuSE Red Hat), FreeBSD Sun Solaris ( 8 9). MySQL . , .
AIX 4.x, 5.x . . 2.6.5.3. Amiga. BSDI 2.x MIT-threads. . 2.6.4.5.
BSDI 3.0, 3.1 4.x . . 2.6.4.5. DEC UNIX 4.x . . 2.6.5.5. FreeBSD
2.x MIT-threads. . 2.6.4.1. FreeBSD 3.x 4.x . . 2.6.4.1. FreeBSD
4.x LinuxThreads. . 2.6.4.1. HP-UX 10.20 DCE- MIT-threads. .
2.6.5.1. HP-UX 11.x . . 2.6.5.2.

Linux 2.0+ Linux Threads 0.7.1+ glibc 2.0.7+. . 2.6.1. Mac OS X.
. 2.6.2. NetBSD 1.3/1.4 Intel NetBSD 1.3 Alpha ( GNU make). .
2.6.4.2. Novell NetWare 6.0. . 2.2.4. OpenBSD > 2.5 , OpenBSD
< 2.5 MIT-threads. . 2.6.4.3. OS/2 Warp 3, FixPack 29 OS/2 Warp
4, FixPack 4. . 2.6.6. SCO OpenServer FSU Pthreads. . 2.6.5.8.

78

2. MySQL SCO UnixWare 7.1.x. . 2.6.5.9. SGI Irix 6.x . .
2.6.5.7. Solaris 2.5 SPARC Intel. . 2.6.3. SunOS 4.x MIT-threads. .
2.6.3. 64 Unix.

Windows 9x, Me, NT, 2000 . . 2.2.1. MySQL . , , MySQL, : . ,
MySQL , , . (SMP). , , , , . , (mutex) . pthread_mutex_lock() ,
MySQL. , MySQL. . , , , . MySQL AB. , . MySQL. , . , MySQL . , .
MySQL 86, SuSE Linux 2.4 ReiserFS ( Linux), SPARC Solaris (2.7-9).
FreeBDS , , , . , — , MySQL —

2.1.

79

, . , MySQL. , , , , , MySQL , MySQL i t e r n a l s (.
1.7.1.1). , , , () . — MySQL. , , . , , , . , , MySQL.

2.1.2. MySQL MySQL , . MySQL , , . , , . .

2.1.2.1. MySQL , , , : () . MySQL , : MySQL 5.0 — , . ( )
BitKeeper. . MySQL 4.1 — , , . . . MySQL 4.0 — . . , , . MySQL 3.23
— . , .

, , » «. «-

80

2. MySQL

» , — , » , «. , . , MySQL , , . MySQL 4.0. MySQL, , , , . , , ,
, ( ). , . , , , . , , . , , BitKeeper. , , . , MySQL, , ,
mysql-4.1.2-alpha. : (4) . 4 . (1) — . . (2) — . . . . , . , . , .
, . . alpha () , , 100% . ( ) (News)
http://dev.mysql.com/doc/mysql/en/News.html. — . , , —

2.1.

81

-, . MySQL. beta () , . , , . . — — , — , , , , . gamma () — -,
. . , . , , , , . . , () . MySQL , . , . MySQL , . , , . , , : .
mysql-test . . . . , (. 6.1.4). crash-me. , (. 6.1.4). , , — MySQL
, , . 100 , .

2.1.2.2. MySQL , : . , . , , RPM- Linux DMG Mac OS X. Zip- tar.
:

82

2. MySQL , , . , : ( ), , . . MySQL . MySQL — , mysqld-max. .
4.1.2. RPM- MySQL-Max, RPM- MySQL-server.

, , , MySQL : MySQL . , MySQL , . mysqld , . , , , :
—with-innodb ( MySQL 4.0 ) —with-berkleydb ( ) with-raid
with-libwrap with-names-z-libs ( ) with-debug[=full] mysqld , . , .
MySQL, . (, , ), , . , . BitKeeper, . , MySQL, , . .

2.1.

83

( ) MySQL C++. , — . , . 2.1.2.3. MySQL MySQL AB, MySQL. , — , .
, , . , , , , . . . (News) , , . .
http://dev.mysql.com/doc/mysql/en/News.html. MySQL : . — — , . ()
1-2 . , . / 4-8 . . , , . , , . BitKeeper . , , . ( , !)

2.1.2.4. ! , . MySQL — «» ( «» , MySQL , , , .) , (. 1.8.7). — ,
MySQL. , —

84

2. MySQL

, , () . , , . . , http://bugs.mysql.com/ , MySQL. . , , , , , .
( 90% .) , MySQL. MySQL, , MySQL (3.23.x, 4.0.x ) . — ( — MySQL), .
. 1.8.7. , ( 15 ) . , . , , . 2-3 . , . http://dev.mysql.com mysql
announce (. 1.7.1.1). . MySQL, 4-8 . :
http://downloads.mysql.com/snapshots.php , , , , , — ; ‘ ‘ . , . .
150 , 10. glibc — , .

2.1.

85

2.1.2.5. MySQL, MySQL MySQL , , , . , , , t a r ( . t a r . g z
) . 2.2. Build-tools/Do-compile, tar.gz,
scripts/make_binary_distribution. . , COMP_ENV_INFO CONFIGURE_LINE
bin/mysqlbug, tar- . MySQL : Linux 2.4. 86 2.95.3: CFLAGS=»-O2
-mcpu=pentiumpro» CXX=gcc CXXFLAGS=»-O2 -mcpu=pentiumpro
-felideconstructors» ./configure prefix=/usr/local/mysql
with-extra-charsets=complex enable-thread-safe-client
enable-local-infile enable-assembler disable-shared
with-client-ldflags=-all-static with-mysqld-ldflags=-allstatic
Linux 2.4.xx Intel Itanium 2 (Intel C++ Itanium Compiler 7.0):
CC=ecc CFLAGS=»-02 -tpp2 -ip -nolib_inline» CXX=ecc CXXFLAGS= «-02
-tpp2 -ip -nolib_inline» ./configure prefix=/usr/local/mysql
with-extra-charsets=complex enable-thread-safe-client
enable-local-infile Linux 2.4.xx Intel Itanium (Intel C++ Itanium
Compiler 7.0): CC=ecc CFLAGS=-tppl CXX=ecc CXXFLAGS=-tppl
./configure -prefix* /usr/local/mysql with-extra-charsets=complex
enable-thread-safeclient enable-local-infile Linux 2.4.xx alpha
(Compaq V6.2-505 / Compaq C + + V6.3-006): CC=ccc CFLAGS=»-fast
-arch generic» CXX=cxx CXXFLAGS=»-fast -arch generic -noexceptions
-nortti» ./configure prefix=/usr/local/mysql
with-extracharsets=complex enable-thread-safe-client
enable-local-infile withmysqld-ldflags=-non_shared
with-client-ldflags=-non_shared disable-shared

Linux 2.x.xx ppc 2.95.4: CC=gcc CFLAGS=»-03
-fno-omit-frame-pointer» CXX=gcc CXXFLAGS= «-03
-fno-omitframe-pointer -felide-constructors -fno-exceptions
-fno-rtti» ./configure prefix=/usr/local/mysql localstatedir=
/usr/local/mysql/data libexecdir=/usr/local/mysql/bin
with-extra-charsets=complex enable-threadsafe-client
enable-local-infile disable-shared with-embedded-server
with-innodb

86 Linux 2.4. s390 gcc 2.95.3:

2. MySQL

CFLAGS=n-O2″ CXX=gcc CXXFLAGS=»-O2 -felide-constructors»
./configure prefix=/usr/local/mysql with-extra-charsets=complex
enable-thread-safe-client enable-local-infile disable-shared
with-client-ldflags=-all-static with-mysqld-ldflags=-all-static
Linux 2.4.xx x86_64 (AMD64) gcc 3.2.1: CXX=gcc ./configure
prefix=/usr/local/mysql with-extra-charsets= complex
enable-thread-safe-client enable-local-infile disable-shared Sun
Solaris 8 x86 gcc 3.2.3: CC=gcc CFLAGS=»-O3
-fno-omit-frame-pointer» CXX=gcc CXXFLAGS=»-O3
-fno-oraitframe-pointer -felide-constructors -fno-exceptions
-fno-rtti» ./configure prefix=/usr/local/mysql localstatedir=
/usr/local/mysql/data libexecdir=/usr/local/mysql/bin
with-extra-charsets=complex enablethread-safe-client
enable-local-infile disable-shared with-innodb Sun Solaris 8 SPARC
gcc 3.2: COgcc CFLAGS=»-O3 -fno-omit-frame-pointer» CXX=gcc
CXXFLAGS=»-O3 -fno-omitframe-pointer -felide-constructors
-fno-exceptions — f n o — r t t i » ./configure
prefix=/usr/local/mysql with-extra-charsets=complex
enable-thread-safeclient enable-local-infile enable-assembler
with-named-z-libs=no withnamed-curses-libs=-lcurses disable-shared
Sun Solaris 8 SPARC 64-bit gcc 3.2: CC=gcc CFLAGS=»-O3 -m64
-fno-omit-frame-pointer» CXX=gcc CXXFLAGS=»-O3 -m64
-fno-omit-frame-pointer -felide-constructors -fno-exceptions — f n
o — r t t i » ./configure prefix=/usr/local/mysql
with-extra-charsets= complex enable-threadsafe-client
enable-local-infile with-named-z-libs=no
with-named-curseslibs=-lcurses disable-shared Sun Solaris 9 SPARC
gcc 2.95.3: COgcc CFLAGS=»-O3 -fno-omit-frame-pointer» CXX=gcc
CXXFLAGS=»-O3 -fno-omitframe-pointer -felide-constructors
-fno-exceptions — f n o — r t t i » ./configure
prefix=/usr/local/mysql with-extra-charsets=complex
enable-thread-safeclient enable-local-infile enable-assembler
with-named-curses-libs=-lcurses disable-shared Sun Solaris 9 SPARC
-5.0 (Sun Forte 5.0): CC=cc-5.0 C X C ASFLAGS=»-xarch=v9″
CFLAGS=»-Xa -xstrconst -mt X=C -D_FORTEC_ -xarch=v9″
CXXFLAGS=»-noex -mt -D_FORTEC_ -xarch=v9″ ./configure
prefix=/usr/local/mysql with-extra-charsets=complex
enable-thread-safeclient enable-local-infile enable-assembler
with-named-z-libs=noenable-thread-safe-client disable-shared IBM
AIX 4.3.2 ppc gcc 3.2.3: CFLAGS=»-O2 -mcpu=powerpc -Wa,-many »
CXX=gcc CXXFLAGS=»-O2 -mcpu= powerpc -Wa,-many -felide-constructors
-fno-exceptions — f n o — r t t i » ./configure
prefix=/usr/local/mysql with-extra-charsets=complex
enable-thread-safeclient enable-local-infile with-named-z-libs= no
disable-shared

2.1. IBM AIX 4.3.3 xlC_r (IBM Visual Age C/C++ 6.0): CC=xlc_r
CFLAGS=»-ma -02 -qstrict -qoptimize=2 -qmaxmem=8192″ CXX=xlC_r
CXXFLAGS =»-ma -02 -qstrict -qoptimize=2 -maxmem=8192″ ./configure
prefix=/usr/local/mysql localstatedir=/usr/local/mysql/data
libexecdir=/usr/local/mysql/bin with-extra-charsets=complex
enablethread-safe-client enable-local-infile with-named-z-libs= no
-disableshared with-innodb I B M A I X 5.1.0 gcc 3.3: CFLAGS=»-02
-mcpu=powerpc -Wa,-many» CXX=gcc CXXFLAGS=»-02 -mcpu= powerpc -Wa,
-many -felide-constructors -fno-exceptions -fno-rtti» ./configure
prefix=/usr/local/mysql with-extra-charsets=complex
enable-thread-safeelient enable-local-infile with-named-z-libs= no
disable-shared I B M A I X 5.2.0 x l C r (IBM Visual Age C/C++
6.0): CC=xlc_r CFLAGS=»-ma -02 -qstrict -qoptimize=2 -qmaxmem=8192″
CXX=xlC_r CXXFLAGS=»-ma -02 -qstrict -qoptimize=2 -qmaxmem=8192″
./configure prefix=/usr/local/mysql
localstatedir=/usr/local/mysql/data libexecdir=/usr/local/mysql/bin
with-extra-charsets=complex enablethread-safe-client
enable-local-infile with-named-z-libs= no -disableshared
with-embedded-server with-innodb H P — U X 10.20 pa-riscl.l gcc
3.1: CFLAGS=»-DHPUX -I/opt/dce/include -03 -fPIC» CXX=gcc CXXFLAGS=
«-DHPUX -I/opt/dce /include -felide-constructors -fno-exceptions
-fno-rtti -03 -fPIC» ./configure prefix=/usr/local/mysql
with-extra-charsets=complex enablethread-safe-client
enable-local-infile with-pthread with-named-threadlibs= -Idee
with-lib-ccflags=-fPIC disable-shared H P — U X 11.00 pa-risc aCC
(HP ANSI C + + B3910B A.03.50): CC=cc CXX=aCC CFLAGS=+DAportable
CXXFLAGS=+DAportable ./configure prefix=/usr/local/mysql
localstatedir=/usr/local/mysql/data libexecdir=/usr/local/mysql/bin
with-extra-charsets=complex enablethread-safe-client
enable-local-infile disable-shared with-embeddedserver
with-innodb

87

H P — U X 11.11 pa-risc2.0 64bit aCC (HP A N S I C + + B3910B
A.03.33): CC=cc CXX=aCC CFLAGS=+DD64 CXXFLAGS=+DD64 ./configure
prefix=/usr/local/mysql with-extra-charsets=complex
enable-thread-safe-client enable-local-infile disable-shared H P —
U X 11.11 pa-risc2.0 32bit aCC (HP ANSI C + + B3910B A.03.33):
CC=cc CXX=aCC CFLAGS=»+DAportable» CXXFLAGS=ll+DAportable»
./configure prefix=/usr/local/mysql
localstatedir=/usr/local/mysql/data libexecdir=/usr/local/mysql/bin
with-extra-charsets=complex enable-threadsafe- client
enable-local-infile disable-shared with-innodb H P — U X 11.22 ia64
64bit aCC (HP aC++/ANSI 3910 A.05.50):

88

2. MySQL = = CFLAGS=»+DD64 +DSitanium2″ CXXFLAGS=»+DD64
+DSitanium2″ ./configure prefix=/usr/local/mysql
localstatedir=/usr/local/mysql/data libexecdir=/usr/local/mysql/bin
with-extra-charsets=complex —enablethread-safe-client
enable-local-infile disable-shared with-embeddedserver with-innodb
Apple Mac OS X 10.2 powerpc gcc 3.1: CC=gcc CFLAGS=»-O3
-fno-omit-frame-pointer» CXX=gcc CXXFLAGS=»-O3
-fno-omitframe-pointer -felide-constructors -fno-exceptions — f n o
— r t t i » ./configure prefix=/usr/local/mysql
with-extra-charsets=complex enable-thread-safeclient
enable-local-infile disable-shared FreeBSD 4.7 i386 gcc 2.95.4:
CFLAGS=-DHAVE_BROKEN_REALPATH ./configure prefix=/usr/local/mysql
withextra-charsets=complex enable-thread-safe-client
enable-local-infile enable-assembler with-named-z-libs=not-used
disable-shared FreeBSD 4.7 i386 using LinuxThreads gcc
2.95.4:CFLAGS=»-DHAVE_BROKEN_REALPATH -D_USE_UNIX98 -D_REENTRANT
-D_THREAD_SAFE — I / u s r / l o c a l / i n c l u d e / p t h r e
a d / l i n u x t h r e a d s » CXXFLAGS=M

-DHAVE_BROKEN_REALPATH -D

USEJJNIX98 -D_REENTRANT -D_THREAD_SAFE

-I/usr/local/include/pthread/linuxthreads» ./configure prefix=
/usr/local/mysql localstatedir=/usr/local/mysql/data libexecdir=
/usr/local/mysql/bin enablethread-safe-client enable-local-infile
enable-assembler with-namedthread-libs=M

-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R

-D_THREAD_SAFE — I

/usr/local/include/pthread/linuxthreads -L/usr/local/lib
-11thread -llgcc_r» disable-shared with-embedded-server with-innodb
QNX Neutrino 6.2.1 i386 gcc 2.95.3qnx-nto 20010315: CC=gcc
CFLAGS=»-O3 -fno-omit-frame-pointer» CXX=gcc CXXFLAGS=»-O3
-fno-omitframe-pointer -felide-constructors -fno-exceptions — f n o
— r t t i » ./configure prefix=/usr/local/mysql
with-extra-charsets=complex enable-thread-safeclient
enable-local-infile disable-shared , . MySQL AB , , . SCO Unix
3.2v5.0.6 i386 gcc 2.95.3: CFLAGS=»-O3 -mpentium» LDFLAGS=-static
CXX=gcc CXXFLAGS=»-O3 -mpentium -felideconstructors» ./configure
prefix=/usr/local/mysql with-extra-charsets=complex
enable-thread-safe-client enable-local-infile with-named-z-libs=no
enable-thread-safe-client disable-shared SCO OpenUnix 8.0.0 i386
3.2: CC=cc CFLAGS=»-O» C X C ./configure prefix=/usr/local/mysql
X=C with-extracharsets=complex enable-thread-safe-client
enable-local-infile withnamed-z-libs=no enable-thread-safe-client
disable-shared

2.1.

89

Compaq Tru64 OSF/1 V5.1 732 alpha / (Compaq V6.3-029i / DIGITAL
C++V6.1-027): CC=»cc -pthread» CFLAGS=»-04 -ansi_alias -ansi_args
-fast -inline speed -speculate a l l » CXX=»cxx -pthread»
CXXFLAGS=»-O4 -ansi_alias -fast -inline speed -speculate a l l
-noexceptions -nortti» ./configure prefix=/usr/local/mysql
with-extra-charsets=complex enable-thread-safe-client
enable-local-infile with-prefix= /usr/local/mysql
with-named-thread-libs=»-lpthread -lmach -lexc — l c »
disable-shared with-mysqld-ldflags=-all-static SGI Irix 6.5 IP32
gcc 3.0.1: CC=gcc CFLAGS=»-O3 -fno-omit-frame-pointer»
CXXFLAGS=»-O3 -fno-omit-framepointer -felide-constructors
-fno-exceptions -fno-rtti» ./configure prefix=/usr/local/mysql
with-extra-charsets=complex enable-thread-safeclient
enable-local-infile disable-shared FreeBSD/sparc64 5.0 gcc 3.2.1: C
L G = D A E3 O E _ E L A H ./configure prefix=/usr/local/mysql F A
S — H V JR K N R A P T localstatedir=/usr/local/mysql/data
libexecdir=/usr/local/mysql/bin with-extracharsets=complex
enable-thread-safe-client enable-local-infile -disableshared
with-innodb MySQL AB . , , . Linux 2.2. SPARC egcs 1.1.2: CC=gcc
CFLAGS=»-03 -fno-omit-frame-pointer» CXX=gcc CXXFLAGS=»-O3
-fno-omitframe-pointer -felide-constructors -fno-exceptions
-fno-rtti» ./configure prefix=/usr/local/mysql
with-extra-charsets=complex enable-thread-safeclient
enable-local-infile enable-assembler disable-shared Linux 2.2.x
with x686 gcc 2.95.2: CFLAGS=»-03 -mpentiumpro» CXX=gcc
CXXFLAGS=»-03 -mpentiumpro -felideconstructors -fno-exceptions
-fno-rtti» ./configure prefix=/usr/local/mysql enable-assembler
with-mysqld-ldflags= — a l l — s t a t i c disable-shared
withextra-charsets=complex SunOS 4.1.4 2 sun4c gcc 2.7.2.1 : CC=gcc
CXX=gcc CXXFLAGS=»-03 -felide-constructors» ./configure prefix=
/usr/local/mysql disable-shared with-extra-charsets=complex
enable-assembler SunOS 5.5.1 ( ) sun4u egcs 1.0.3 2.90.27, gcc
2.95.2 : CC=gcc CFLAGS=»-03″ CXX=gcc CXXFLAGS=»-O3
-felide-constructors -fno-exceptions -fno-rtti» ./configure
prefix=/usr/local/mysql with-low-memory withextra-charsets=complex
enable-assembler SunOS 5.6 i86pc gcc 2.8.1:

90

2. MySQL CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure
prefix=/usr/local/mysql with-lowmemory with-extra-charsets=complex
BSDI BSD/OS 3.1 i386 gcc 2.7.2.1: CC=gcc CXX=gcc CXXFLAGS=-0
./configure ~prefix=/usr/local/mysql with-extracharsets=complex
BSDI BSD/OS 2.1 i386 gcc 2.7.2: CC=gcc CXX=gcc CXXFLAGS=-O3
./configure prefix=/usr/local/mysql with-extracharsets=complex AIX
2 4 c gcc 2.7.2.2: CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure
prefix=/usr/local/mysql with-extracharsets=complex

, , MySQL internals (. 1.7.1.1). RPM MySQL 3.22 . MySQL 3.22,
RPM MySQL AB. MySQL, —with-debug with-debug=full

-fomit-frame-pointer.

2.1.3. MySQL , MySQL http://www.mysql.com/. h t t p : //mirrors.
sunsite.dk/mysql/. , MySQL,
http://dev.mysql.com/downloads/mirrors.html. , MySQL .

2.1.4. MD5 GnuPG MySQL , . MySQL AB : MD5. GnuPG (GNU Privacy
Guard). RPM-. . , MD5 GPG- , , , . —

2.1.

91

, [email protected] [email protected], , . .

2.1.4.1. MD5 , MySQL , , MD5 , . , ( _ — ): shell> md5sum _ :
shell> md5sum mysql-standard-4.0.17-pc-linux-i686.tar.gz
60f5fe969d61c8f82e4f7f62657elf06
mysql-standard-4.0.17-pc-linux-i686.tar.gz , ( ) . , md5sum. md5, .
Linux G U Text Utilities, N .
http://www.gnu.org/software/textutils. OpenSSL, openssl md5 _. md5
DOS/Windows http://www.fourmilab.ch/md5.

2.1.4.2. GnuPG . , MD5, , , . MySQL 4.0.10 ( 2003 ) MySQL AB
GnuPG (GNU Privacy Guard). GnuPG — , Pretty Good Privacy (PGP)
(Phil Zimmermann). GnuPG, , , http://www.gnupg.org. Linux GnuPG, .
OpenPGP http://www.openpgp.org. GPG MySQL AB. http: //www.
keyserver.net. , , [email protected] : Key ID: pub 1024D/5072E1F5
2003-02-03 MySQL Package signing key (www.mysql.com) Fingerprint:
A4A9 4068 76FC BD3C 4567 70C8 8C71 8D3B 5072 E1F5 Public Key
(ASCII-armored):

92

2. MySQL BEGIN PGP PUBLIC KEY BLOCK Version: GnuPG vl.0.6
(GNU/Linux) Comment: For info see http://www.gnupg.org
mQGiBD4+owwRBAC14GIfUfCyEDSIePvEW3SAFUdJBtoQHH/nJKZyQT7h9bPlUWC3RODjQReyCITR
rdwyrKUGku2FmeVGwn2u2WmDMNABLnpprWPkBdCk96+OmSLN9brZfw2vOUgCmYv2hW0hyDHuvYlQ
A/BThQoADgj8AW6/0Lo7VlW9/8VuHP0gQwCgvzV3BqOxRznNCRCRxAuAuVztHRcEAJooQKl+iSiu
nZMYDlWufeXfshc57S/+yeJkegNWhxwR9pRWVArNYJdDRT+rf2RUe3vpquKNQU/hnEIUHJRQqYHo
8gTxvxXNQc7fJYLVK2HtkrPbP72vwsEKMYhhr0eKCbtLGfls9krjJ6sBgACyP/Vb7hiPwxh6rDZ7
ITnEkYpXBACmWpP8NJTkamEnPCia2ZoOHODANwpUkP43I7jsDmgtobZX9qnrAXw+uNDIQJEXM6FS
bi0LLtZciNlYsafwAPEOMDKpMqAK6lyisNtPvaLd81H0bPAnWqcyefeprv0sxxqUEMcM3o7wwgfN
83POkDasDbs3pjwPhxvhz6//62zQJ7Q7TXlTUUwgUGFja2FnZSBzaWduaW5nIGtleSAod3d3Lml5
c3FsLmNvbSkgPGJlaWxkQG15c3FsLmNvbT6lXQQTEQIAHQUCPj6jDAUJCWYBgAULBwoDBAMVAwID
FgIBAheAAAoJEIxxjTtQcuHlcY4AnilUwTXn8MatQOiG0a/bPxrvK/gCAJ4oinSNZRYTnblChwFa
azt7PF3qzIhMBBMRAgAMBQI+PqPRBYMJZgC7AAoJEElQ4SqycpHyJOEAnlmxHijft00bKXvucSo/
pECUmppiAJ41M9MRVj5VcdH/KN/KjRtW6tHFPYhMBBMRAgAMBQI+QoIDBYMJYiKJAAoJELblzU3G
uiQ/lpEAoIhpp6BozKI8p6eaabzF5MUH58pAKCu/ROofK8JEg2aLos+5zEYrB/LsrkCDQQ+PqMd
EAgA7+GJfxbMdY4wslPnjH9rF4N2qfWsEN/lxaZoJYc3a6M02WCnH16ahT2/tBK2wlQI4YFteR47
gCvtgb6OUHffOo2HfLmRDRiRjdlDTCHqeyX7CHhcghj/dNRlW2Z015QFEcmV9U0Vhp3aFfWC4Uj
fs3LU+hkAWzE7zaD5cH9J7yv/6xuZVw411x0h4UqsTcWMu0iMlBzELqXlDY7LwoPEb/O9Rkbf4fm
LellEzIaCa4PqARXQZc4dhSinMt6K3X4BrRsKTfozBu74F47D8Ilbf5vSYHbuE5p/loIDznkg/p8
kW+3FxuWrycciqFTcNz215yyX39LXFnlLzKUb/F5GwADBQf+Lwqqa8CGrRfsOAJxim63CHfty5mU
c5rUSnTslGYEIOCRlBeQauyPZbPDsDD9MZlZaSafanFvwFG6Llx9xkU7tzq+VKLoWkm4u5xf3vn5
5VjnSdlaQ9eQnUcXiL4cnBGoTbOWI39EcyzgslzBdC++MPjcQTcA7p6JUVsP6oAB3FQWg54tuUo0
Ec8bsM8b3Ev42LmuQT5NdKHGwHsXTPtl0klk4bQk4OajHsiylBMahpT27jWjJlMiJc+IWJ0mghkK
Ht926s/ymfdf5HkdQlcyvsz5tryVI3Fx78XeSYfQvuuwqp2H139pXGEkg0n6KdUOetdZWhe70YGN
PwlyjWJTlIhMBBgRAgAMBQI+PqMdBQkJZgGAAAoJEIxxjTtQcuH17p4An3rlQpVC9yhnW2cSAjq+
kr72GX0eAJ4295kl6NxYEuFApmrl+0uUq/SlsQ===YJkx END PGP PUBLIC KEY
BLOCK

GPG gpg —import. , mysqljpubkey.asc, : shell> gpg import
mysqljpubkey.asc GPG , . MySQL , . , , .asc. : Distribution file
mysql-standard-4.0.17-pc-linux-i686. t a r . gz Signature file
mysql-standard-4.0.17-pc-linux-i686.tar.gz.asc , , : shell> gpg
verify _.as : shell> gpg verify
mysql-standard-4.0.17-pc-linux-i686.tar.gz.asc gpg: Warning: using
insecure memory!

2.1. gpg: S i g n a t u r e made Mon 03 Feb 2003 08:50:39 P M T
M E u s i n g DSA key ID 5072E1F5 gpg: Good s i g n a t u r e from
«MySQL Package s i g n i n g key (www.mysql.com) «

93

«Good signature…» , . » u s i n g i n s e c u r e memory» (»
«) .

2.1.4.3. RPM RPM . GPG MD5. : s h e l l > rpm checksig ^.rpm
: s h e l l > rpm checksig MySQL-server-4.0.10-0.i386.rpm
MySQL-server-4.0.10-0.i386.rpm: md5 gpg O K ! RPM 4.1, : «(GPG) NOT
OK (MISSING KEYS: GPG#5072e1f5)»f MySQL GPG, RPM. , RPM 4.1 GPG (
GPG). , , GPG — , . MySQL RPM, , . rpm import . , mysql_pubkey. as
, : s h e l l > rpm import mysql_pubkey.asc

2.1.5. , , MySQL AB. , , . Windows C:raysql, : bin data Docs
examples include lib scripts share mysqld. . . . . . . .

94

2. MySQL

, RPM- Linux, : /usr/bin /usr/sbin /var/lib/mysql /usr/
share/doc/packages include/usr/include/mysql lib/usr/lib/mysql
/usr/share/mysql sql-bench/usr/share/sql-bench . mysqld. . . . . .
.

Unix t a r ( /usr/ l o c a l /mysql). : bin data docs include
lib scripts share/mysql sql-bench mysqld. . . . . mysql_install_db.
. .

. /usr/local: bin include /mysql info lib/mysql libexec share
/mysql sql-bench var . . Info-. . mysqld. . . .

: mysqld l i b e x e c bin. — v a r data. m y s q l _ i n s t a
l l _ d b b i n s c r i p t s . — , i n c l u d e /mysql lib/mysql
include lib.

2.2. MySQL

95

scripts/make_binary_distribution, .

2.2. MySQL MySQL , (, » «). . 2.2.5 , . 2.1 , .

2.2.1. MySQL Windows MySQL Windows : 1. . 2. , . 3. , . 4. . 5.
MySQL. MySQL Windows : , , , . , , VC++ 6.0. , . , , MySQL. , MySQL
Windows . 2.3.6. 2.2.1.1. Windows MySQL Windows : 32- Windows, 9,
Me, NT, 2000 . NT (Windows NT, 2000 ) MySQL (. 2.2.1.7). TCP/IP.
Windows, http://dev.mysql.com/downloads (. 2.1.3). 1 !Jf FTP FTP-,
‘v. , .

96

2. MySQL WinZip Windows-, .zip, . , , . MySQL ODBC, MyODBC. 4 ,
MySQL NTFS . M X O S AVGROWLENGTH. AR W

2.2.1.2. Windows MySQL Windows : 1. Windows NT, 2000 , , . 2.
MySQL, MySQL. Windows NT, 2000 , Windows, : :> NET STOP MySQL
(, mysqld-max mysqld), : :> C:mysqlbinmysqld remove , .
MySQL , : :> C:raysqlbinmysqladmin -u root shutdown 3.
WinMySQLAdmin, . 4. . 5. setup.exe, . MySQL , (C:mysql), Browse ()
. MySQL , . — , 2.2.1.3. 6. .

! — MySQL 4.1 Windows . 4.1 — Zip-, , MySQL. , m y s q l — 4 . 1
. 1 alpha-win.zip C:mysql, :, mysql-4.1.1-alpha mysql.

2.2. MySQL

97

MySQL 4.1, data, . 4.1 , , data — . : raysql . MySQL 4.1, ,
data data. . (. 2.5.8).

2.2.1.3. MySQL- Windows — , . , , , MySQL. , : (: mysql :
mysqldata). . , innoDB MySQL 3.23, , 9.4. ( MySQL 4.0, InnoDB
data. , InnoDB . , — , .) Windows MySQL , MySQL. , . , MySQL
C:mysql, — : mysqldata. , , : raydata, : : mysqldata :
mydata. datadir . Windows MySQL : my. ini Windows : my. cnf.
Windows : I D W WN O S : WinNT. WINDIR; : :> echo %WINDIR%
MySQL my.ini, my.cnf. . (boot loader), : , — my. ini. , . Notepad
(). , MySQL

98

2. MySQL

E:mysql, E:mydatadata, [mysqld] basedir datadir: [mysqld] #
set basedir to your i n s t a l l a t i o n path # basedir
basedir=E:/mysql # set datadir to the location of your data
directory # datadir datadir=E:/raydata/data , Windows ( ) ( ). , .
WinMySQLAdmin. bin MySQL, . WinMySQLAdmin , : WinMySQLAdmin my.
ini. WinMySQLAdmin C:my.cnf, :my_cnf .bak, . .

2.2.1.4. Windows MySQL 3.23.38, Windows , MySQL-Max. MySQL, , .
mysqld , , InnoDB BDB. . 4.0, InnoDB. 4.0 . Windows NT, 2000 .
InnoDB BDB. To , mysqld-max, .

mysqld-opt

mysqld-nt mysqld-max mysqld-max-nt

Intel, Intel i386 . MySQL TCP/IP Windows-, mysqld-nt
mysqld-max-nt Windows NT, Windows 2000 Windows . , , —

2.2. MySQL

99

TCP/IP. ( Windows , TCP/IP.) : MySQL 3.23.50, ,
enabled-named-pipe. , , . mysqld-nt mysqld-max-nt, Windows, (NT,
2000, ). Windows 98 Windows Me, TCP/IP. . Windows 95 . % !? |?
& il t; 1 mysqld. , mysqld-opt, , . pa , , mysqld , , , ,
Windows.

2.2.1.5. Windows 95, 98 Me MySQL TCP/IP. ( MySQL .) MySQL ,
TCP/IP. TCP/IP — Windows. , Windows 95 (, OSR2), , Winsock. MySQL
Winsock 2. Winsock http://www.microsoft.com. Windows 98 Winsock 2,
. NT- , Windows NT, 2000 , TCP/IP (, ). 2.2.1.4. MySQL. Windows. ,
MySQL , C:mysql. MySQL , , . ( » DOS»). . — , .

102

2. MySQL

MySQL TCP/IP Windows NT, Service Pack 3 ( ). MySQL Windows , . :
:> C:mysqlbinmysqladmin -u root shutdown mysqladmin, . root
— MySQL. , MySQL , Windows. : :> mysqld —install mysqld , :
:> C:mysqbinmysqld install MySQL 4.0.2 i n s t a l l . MySQL
4.0.3 defaults-file, , . , , . , , MySQL, [mysqld] . i n s t a l l
, [mysqld] , . defaults-file , [mysqld] .

? !% ‘^ || & MySQL 4.0.17 , Windows, , . MySQL : Program F
i l e s , .

, i n s t a l l , , MySQL. : :> :mysqlbinmysqld install
mysqldefaults-file=C:my-opts.cnf — — i n s t a l l .
defaults-file, [mysql] ( , ). , defaults-file , , [mysqld]. Start
parameters ( ) Windows Services.

2.2. MySQL

103

, MySQL , Windows. Services NET START MySQL. NET . MySQL , , .
mysqld , . : mysqldata .err. mysqld , Services, NET STOP MySQL
mysqladmin shutdown. Windows , . MySQL 3.23.44, Manual, , MySQL . —
— i n s t a l l install-manual: :> :mysqlbinmysqld
install-manual , , . —remove : :> :mysqlbinmysqld remove
MySQL 3.23.49 , : Windows , , , . (, InnoDB ). MySQL 3.23.49,
Windows MySQL . , , MySQL . mysqladmin shutdown. Windows 2000 .
Windows NT, 20 , . , winntsystem32regedt32.exe
WaitToKillServiceTimeout
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControl . (, 120000
120- ). mysqld , . 2.2.1.6. 2.2.1.8. MySQL Windows , MySQL : :>
:mysqlbinmysqlshow :> C:mysqlbinmysqlshow -u root mysql
:> C:mysqlbinmysqladmin version status proc C:>
C:mysqlbinmysql test

104

2. MySQL

mysqld TCP/IP- Windows 9x/Me, , (DNS). mysqld skip-name-resolve
localhost IP- Host MySQL. MySQL TCP/IP, pipe (.) . socket . MySQL
4.1 ptorocol=PIPE. MySQL: mysql mysql Windows, . gnus GNU ,
readline.

mysql , cygwinbl9.dll, , mysqlc . MySQL , mysqlc ( bin MySQL).
cygwinbl9.dll bin, l i b Windows (WindowsSystem ).

2.2.1.9. MySQL Windows MySQL Unix MySQL Windows . MySQL Windows
, Unix, : Windows 95 . Windows 95 200 . MySQL , mysqld Windows 95,
! Windows . . Windows 4000 , , , , , . , , , — . MySQL , , . , , ;
, MySQL, . . MySQL pread() pwriteO, INSERT SELECT. (mutexes)
pread()/pwrite (). — , readfile()/writefile Windows NT, 2000 .
—

2.2. MySQL

105

MySQL 1024, , NT, 2000 , Unix. . MySQL , , : , Unix- MySQL. «»,
MySQL. mysqladmin k i l l . mysqladmin . shutdown , —

, Windows- » «. ALTER TABLE. ALTER TABLE, . , Windows , . , , .
DROP TABLE. DROP

Автор: Артеменко Ю.Н.
Издательство: Вильямс

Год: 2005
Страниц: 622
Язык: Русский
Формат: PDF (Отсканированные страницы)
ISBN: 5-8459-0805-1

Аннотация:

Эта книга, написанная специалистами компании MySQL AB, является всеобъемлющим справочником по установке, обслуживанию и администрированию сервера баз данных MySQL. По сути — это официальная документация фирмы производителя. В книге рассмотрен весь спектр вопросов, касающихся администрирования MySQL, а также представлена информация, предназначенная для опытных пользователей и администраторов.
Как известно, MySQL занимает лидирующие позиции среди множества систем управления базами данных с открытым исходным кодом. Благодаря высокой производительности и простоте настройки, богатому выбору API-интерфейсов, а также функциональным средствам работы с сетями, сервер MySQL стал одним из наиболее удачных вариантов для разработки Web-приложений, взаимодействующих с базами данных.
Книга рассчитана на администраторов и разработчиков Web-приложений любой квалификации, а также на студентов и преподавателей соответствующих дисциплин.

Скачать книгу из интернета: