##  : mysql 3.21 Reference Manual PostScript Ŵ
( ֽ Ŵ 3.22 ۷ Ŵ̸ 3.22.14b-gammaԴϴ.)

5, 6, 9, 10, 11, 12, 13, 15, 17  : (taejun@hitel.net)
18  C-API κ  : ǿ (golmong@cre.co.kr) 

ġ õ κ kldp.org database ׸񿡼 õ  Ƿ Ͻø ˴ϴ.



0. 
1. MySQL Ϲ 
2. MySQL ϸ Ʈ   û , () ˸ 
3. ̼ / mysql ؾ  
4. mysql ġ
5. mysql ǥ ȣȯ
6. mysql   ý
7. mysql  ۷
8. SQL  
9. mysql  
10. mysql ִ   
11. mysql ġũ
12. mysql ƿƼ
13. ̺   ļ  isamchk ϱ
14. mysql ο  ߰
15. mysql ODBC 
16. Ϲ   
17. Ϲ  ذ 
18. mysql Ŭ̾Ʈ  API
19. ٸ ͺ̽ 

η A ; MYSQL 
η B ;  α׷
η C ;MYSQL 
η D ; MYSQL  
η E ; MYSQL ˷   迡  κ
η F ; MYSQL 巡 ߰ҳ(The TODO)
η G ; ٸ ýۿ ϱ
η H ; MYSQL ǥ 
η I ; Unireg 
η J ; MS ü MYSQL  ̼
η K ; MS ü MYSQL ̼
SQL , Ÿ, Լ ε
 ε

1. MySQL Ϲ 
1.1 MYSQL ΰ?
1.2 Ŵ Ͽ
        1.2.1 Ŵ󿡼  
1.3 MYSQL 
1.4 ֿƯ¡
1.5 
1.6 2000 
1.7 Ϲ SQL   ڷ
1.8  ũ

2. MySQL ϸ Ʈ   û , () ˸ 
2.1 mysql ϸ Ʈ
2.2 ϱ,  ˸ 
2.3 ׳  ˸ 
        2.3.1 mysql ջǾ
2.4 ϸƮ  亯  ȳ

3. ̼ / mysql ؾ  
3.1 mysql 
3.2    
        3.2.1 ⺻ ̸ 
        3.2.2 Ȯ ̸ 
        3.2.3 login 
        3.2.4 Ȯ login 

3.3 ̼̳  ϴ 
3.4 ̼/   
3.5  ۱ 
3.6 mysql   ʰ  Ǹϴ 
3.7 mysql ؾ   ִ ǰ Ǹϴ 
3.8 mysql Ͽ    ϴ 
3.9  Perl/Tcl/PHP/Ÿ ø̼ Ǹϴ 
3.10  ̼ ȭ

4. mysql ġ
4.1 mysql ϱ
4.2 mysql ϴ ü
4.3   ִ mysql 
4.4 Ʈ   ñ
4.5 ġ 
4.6 ̳ʸ ġϱ
        4.6.1 Ŭ̾Ʈ α׷ 
        4.6.2 ý  
                4.6.2.1 
                4.6.2.2 HP-UX
4.7 ҽ ġϱ
        4.7.1  ġ
        4.7.2 ġ ϱ
        4.7.3 Ϲ  ɼ
4.8 Ͽ  涧
4.9 MIT-pthreads ǻ
4.10  ġ 
        4.10.1  DBI/DBD ̽  
4.11 ý  
        -ֶ󸮽, ֶ󸮽 x86, OS4, -DEC-н, -DEC-OSF1,
         SGI-IRIX, FreeBSD, BSD/OS 2, SCO, SCO Unixware 7.0, IBM-AIX, HP-UX
        - : -x86,  5.0/5.1, -,-,MkLinux
          RPM
4.12 TcX ̳ʸ
4.13 Win32 û
4.14 ġ ¾  ׽Ʈ
        4.14.1 mysql_install_db  
        4.14.2 mysql   
        4.14.3 ڵ / Ű 
        4.14.5 ɼ 
4.15 ׷̵/ٿ׷̵Ҷ ǻ
        4.15.1 3.21 3.22 ׷̵
        4.15.2 3.20 3.21 ׷̵
        4.15.3 ٸ Űķ ׷̵

5. mysql ǥ ȣȯ
5.1 mysql ANSI SQL92 Ȯ κ
5.2 mysql  
        5.2.1 sub-selects
        5.2.2 SELECT INTO TABLE
        5.2.3 Ʈ
        5.2.4  ν/Ʈ
        5.2.5 ܷŰ
                5.2.5.1 ܷŰ  ʴ 
        5.2.6 
        5.2.7 '--'  ּ 
5.3 mysql  ִ ǥ
5.4 BLOB/TEXT Ÿ ѻ
5.5 COMMIT-ROLLBACK  ϴ 

6. mysql   ý
6.1   ý̶ ΰ
6.2 mysql  ϱ
        6.2.1 йȣ ϰ ϱ
6.3 mysql ϴ  
6.4  ý ۵ 
6.5   1 :  
6.6   2 : û 
6.7 Ѻ  
6.8 ʱ mysql  
6.9mysql ο   ߰
6.10 йȣ  
6.11     
6.12 ũĿ Ͽ mysql ϰ  

7. mysql  ۷
7.1  : ڿ  ϱ
        7.1.1 ڿ
        7.1.2 
        7.1.3 NULL 
        7.1.4 ͺ̽, ̺, ε, ÷, ˸ƽ ̸
                7.1.4.1 ̸ ҹ 
7.2 ÷ Ÿ
        7.2.1 ÷ Ÿ  䱸
        7.2.2  Ÿ
        7.2.3 ¥/ð Ÿ
                7.2.3.1 DATETIME, DATE, TIMESTAMP Ÿ
                7.2.3.2 TIME Ÿ
                7.2.3.3 YEAR Ÿ
        7.2.4 ڿ Ÿ
                7.2.4.1 CHAR, VARCHAR Ÿ
                7.2.4.2 BLOB, TEXT Ÿ
                7.2.4.3 ENUM Ÿ
                7.2.4.4 SET Ÿ
        7.2.5 ÷  Ÿ 
        7.2.6 ÷ ε
        7.2.7  ÷ ε
        7.2.8 ٸ ͺ̽  ÷ Ÿ 
7.3 SELECT  WHERE  Լ(functions)
        7.3.1 ׷(Group)  
        7.3.2 
        7.3.3 Ʈ(Bit) Լ
        7.3.4 (logical) 
        7.3.5  
        7.3.6 ڿ  Լ
        7.3.7 帧 (control flow) Լ
        7.3.8  Լ
        7.3.9 ڿ Լ
        7.3.10 ¥/ð Լ
        7.3.11 Ÿ Լ
        7.3.12 GROUP BY  ϴ Լ
7.4 CREATE DATABASE
7.5 DROP DATABASE
7.6 CREATE DATABASE
        7.6.1 ڵ ÷ ȯ
7.7 ALTER TABLE
7.8 OPTIMIZE TABLE
7.9 DROP TABLE
7.10 DELETE
7.11 SELECT
7.12 JOIN
7.13 INSERT
7.14 REPLACE
7.15 LOAD DATA INFILE
7.16 UPDATE
7.17 USE
7.18 FLUSH(ĳ )
7.19 KILL
7.20 SHOW(̺, ÷  )
7.21 EXPLAIN(SELECT   )
7.22 DESCRIBE(÷  )
7.23 LOCAK TABLES/UNLOCK TABLES
7.24 SET OPTION
7.25 GRANT / REVOKE
7.26 CREATE INDEX(ȣȯ )
7.27 DROP INDEX(ȣȯ )
7.28 ּ
7.29 CREATE FUNCTION/DROP FUNCION
7.30   ϴ 

8. SQL  
        8.1 twin project 
                8.1.1 Find all non-distributed twins
                8.1.2 Show a table on twin pair status

9. mysql  
        9.1 mysql ϴ 
                9.1.1 Ϳ  ϴ  
                9.1.2 ο ڼ ߰
                9.1.3 ƼƮ  
        9.2 Ʈ α
        9.3 mysql ̺ ִ ũ

10. mysql ִ   
10.1  ũ 
10.2 ޸  
10.3 ӵ   ġ /ũ 
10.4 ε 
10.5 WHERE  ȭϱ
10.6 ̺  ݴ 
        10.6.1 ͺ̽   ̺ 鶧 
10.7  ̺  
10.8 ͺ̽ ̺ ɺ ũ 
10.9 ̺  Ŵ 
10.10 ̺  ۰ 迭ϴ 
10.11 INSERT  ӵ  ġ κ
10.12 DELETE  ӵ  ġ κ
10.13 mysql ִ ӵ  
10.14 ο ˰ ٸ  ΰ?  VARCHAR/CHAR ؾ ϴ°?

11. mysql ġũ

12. mysql ƿƼ
        12.1  ٸ mysql α׷ 
        12.2 ؽƮ Ͽ  
        12.3 mysql  б  ̺ 

13. ̺   ļ  isamchk ϱ
        13.1 isamchk ɾ 
        13.2 isamchk ޸ 
        13.3 ̺  
        13.4 ̺  
        13.5 ļ  isamchk ϱ
                13.5.1   ̺  
                13.5.2 ̺  
                13.5.3 ̺ ȭϱ

14. mysql ο  ߰
        14.1 ο    ߰
                14.1.1 UDF calling sequences
                14.1.2 Argument processing
                14.1.3 Return values and error handling
                14.1.4      ġϱ

        14.2 ο native  ߰

15. mysql ODBC 
        15.1 MyODBC ϴ ü
        15.2 MyODBC  
        15.3 MyODBC ۵ϴ α׷
        15.4 ODBC  α׷  
        15.5 ODBC AUTO_INCREMENT ÷  

16. Ϲ   
        16.1 mysql  Ϲ 
                16.1.1 mysql    
                16.1.2 local mysql   ȵǴ 
                16.1.3 Host '...' is blocked 
                16.1.4 Out of Memory 
                16.1.5 Packet too large 
                16.1.6 The table is full 
                16.1.7 Commands out of sync (Ŭ̾Ʈ)
                16.1.8 Ignoring user 
                16.1.9 Table 'xxx' doesn't exist 
        16.2 mysql ũ  á
        16.3 ؽƮ Ͽ SQL  ϱ
        16.4 mysql ӽ  ϴ 
        16.5 /tmp/mysql.sock  ʵ ȣϴ 
        16.6 Access denied 
        16.7 Ϲ ڷ mysql ϱ
        16.8  ۹̼ 
        16.9 File not found
        16.10 DATE ÷ Ҷ 
        16.11 ˻ ҹ 
        16.12 NULL  
        16.13 alias 
        16.14 õ ̺ ڵ带 
        16.15 شϴ ڵ尡   ذ
        16.16 ALTER TABLE  

17. Ϲ  ذ 
        17.1 ͺ̽ 
        17.2 ͺ̽ 
        17.3  ӽſ  mysql  ϱ

18. mysql Ŭ̾Ʈ  API
        18.1 mysql C API
        18.2 C API ŸŸ
        18.3 C API Լ 
        18.4 C API Լ 
                ****
                18.4.47 mysql_query() ߴµ mysql_store_result()
                         NULL ȯϴ 
                18.4.48 ǿ   
                18.4.49  Էµ unique ID  
                18.4.50 C API ũҶ 
                18.4.51 thread-safe Ŭ̾Ʈ 
        18.5 mysql perl API
                18.5.1 DBI with DBD :: mysql
                        18.5.1.1 dbi ̽
                        18.5.1.2 DBI/DBD ߰ 
        18.6MYSQL ڹ (JDBC)
        18.7 mysql PHP API
        18.8 MYSQL C++ API
        18.9 mysql Python API
        18.10 Mysql TCL API

19. ٸ ͺ̽ 
        19.1 mSQL 
                19.1.1 mSQL  mysql ű
                19.1.2 msql mysql Ŭ̾Ʈ/   
                19.1.3 msql 2.0 mysql SQL  
        19.2 PostgreSQL 


η A ; MYSQL 
η B ;  α׷
η C ;MYSQL 
η D ; MYSQL  
η E ; MYSQL ˷   迡  κ
η F ; MYSQL 巡 ߰ҳ(The TODO)
η G ; ٸ ýۿ ϱ
η H ; MYSQL ǥ 
η I ; Unireg 
η J ; MS ü MYSQL  ̼
η K ; MS ü MYSQL ̼
SQL , Ÿ, Լ ε
 ε
5. mysql ǥ ȣȯ

5.1 mysql ANSI SQL92 Ȯκ
mysql ٸ sql ͺ̽ ã   Ȯ κ ִ. ̷ κ 
ϴ  ؾ Ѵ. ֳĸ  mysql  ڵ尡 ٸ  SQL    
  ֱ ̴.  쿡 /*! ... */  ּ  MYSQL Ȯ 
̿ ð ڵ带   ִ.  :

SELECT /*! STRAIGHT_JOIN */ col_name from table1,table2 WHERE ...

MYSQL Ȯ κ  :
- ʵŸ MEDIUMINT, SET, ENUM , ׸ ٸ BLOB  TEXT Ÿ.
- ʵӼ AUTO_INCREMENT, BINARY, UNSIGNED and ZEROFILL.
-  ڿ 񱳴 ⺻  ҹڸ     ڼ(⺻ 
ISO-8859-1 Latin1)    ȴ. ̰    ÷ BINARY 
Ӽ ؾ ϸ ̷ 쿡 mysql  ȣƮ ϴ ASCII   
ڿ Ѵ. 
- MYSQL ͺ̽ 丮  ̺ ̸ . ̰ 
 ϰ ִ:
     ̸ ҹڸ ϴ (κ  н ý.  
     ~)   ýۿ MYSQL ͺ̽ ̸  ̺ ̸ ҹڸ 
     Ѵ. ̺ ̸ ϴµ  ִٸ   ҹڷ .
     ̺ , ̸ٲٱ, ű, ,  縦  ǥ ý    
      ִ.   ̺ ̸ ٲٷ  شϴ ̺ `.ISD', `.ISM' and 
     `.frm'  ̸ ٲٸ ȴ.
- SQL db_name.tbl_name  ̿Ͽ  ٸ ͺ̽ ̺    
ִ. Ϻ SQL     ̰ User space θ. MYSQL 
    TABLESPACES    ʴ´  :  create  table  ralph.my_table...IN 
my_tablespace. 
- ġ() ÷ LIKE   ִ.
- SELECT   INTO OUTFILE    STRAIGHT_JOIN       ִ.  7.11 
[SELECT] .
-EXPLIAN SELECT ̺   Ǿ   ش.
- Use of index names, indexes on a subpart of a field, and use of INDEX or KEY in a 
CREATE TABLE statement. 7.6 [CREATE TABLE] .
- ALTER TABLE CHANGE col_name, DROP col_name Ǵ DROP INDEX  
Ѵ. 7.7 [ALTER TABLE] .
- ALTER TABLE  IGNORE .
- ALTER TABLE   ADD, ALTER, DROP or CHANGE 
- IF EXISTS Ű带 ̿ DROP TABLE .
-  ̺ ̻󿡼 DROP TABLE .
- LOAD DATA INFILE . κ     Ŭ LOAD DATA INFILE
 ȣȯȴ. 7.15 [LOAD DATA INFILE] .
(**   ͸ Ѳ Է   INSERT  ϴ  ͺ ӵ 
. **)
- OPTIMIZE TABLE  .
- ڿ '''  ƴ϶ '"' Ǵ '''    ִ.
- escape `\'  .
- SET OPTION . 7.24 [SET OPTION] .
- GROUP BY κп  ÷  ʿ䰡 . ̷   Ϲ ǰ 
ƴ Ư ǿ  Ų. 7.3.12 [GROUP BY Functions] .
(** ANSI SQL  Թ ̿Ͽ GROUP  BY   ϰ ϴ 
 ÷ GROUP BY  ־ Ѵ. ̷ Ǵ  ʿ   
 ִµ MYSQL ̷   ̴. 7.3.12 Ѵ **)
- ٸ SQL ȯ ߴ ڸ  MYSQL   ɿ ˸ƽ 
.      ANSI SQL  ODBC  Ѵ.
- MYSQL C α׷    OR  AND ǹϴ ||  &&  
Ѵ. MYSQL ||  OR   ̸ &&  AND  ̴. ̷ 
  MYSQL string  concatenation(ڿ , ?)   ANSI SQL 
۷ ||   ʴ´.  CONCAT() Ѵ. CONCAT()   
ڰ ־ MYSQL  || ۷  ȯϱ .

MySQL understands the || and && operators to mean logical OR and AND, as in the C 
programming language. In  MySQL, ||  and OR are  synonyms, as  are &&  and AND. 
Because of this  nice syntax,  MySQL doesn't support  the ANSI  SQL operator  || for 
string concatenation; use  CONCAT() instead.  Since CONCAT() takes  any number  of 
arguments, it's easy to convert use of the || operator to MySQL. 

-  ϵ SQL CODE  STRAIGHT_JOIN MYSQL Ű  
ϱ  ̷ Ű带 /* */ ּȿ   ִ. ּ  '!'  
Ѵ. ̷   MYSQL ּ ٸ  MYSQL    ؼ ٸ 
SQL  ̷ Ȯ  ʰ ǳʶ  ִ.  :

SELECT /*! STRAIGHT_JOIN */ * from table1,table2 WHERE ...

-  ̳  :
     CREATE DATABASE or DROP DATABASE. 7.4 [CREATE DATABASE] .
     MOD() ſ % . % C α׷Ӹ  ϸ  PostgresSQL 
     ȣȯ  Ѵ.
     ÷  =, <>, <= ,<, >=,>, <<, >>, AND, OR, LIKE .
     LAST_INSERT_ID(). 18.4.49 [mysql_insert_id()] .
     REGEXP or NOT REGEXP.
     ϳ ϳ ̻ ڸ  CONCAT()  CHAR(). MYSQL ̷ 
       ڸ   ִ.
      BIT_COUNT(),   ELT(),  FROM_DAYS(),   FORMAT(), IF(),   PASSWORD(), 
     ENCRYPT(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), or WEEKDAY(). 
     꽺Ʈ ִ  TRIM()  .(Use of TRIM() to trim  substrings) ANSI 
     SQL    Ÿ Ѵ.
(** ~ Ʈ ԰ ϴ Ʈ ƴ϶ϴ... **)
 ׷ ǿ STD(), BIT_OR() and BIT_AND() 
 DELET + INSERT  REPLACE . 7.14 [REPLACE] .
 FLUSH flush_option .


5.2 MYSQL  
 ɵ   MYSQL  ִ.   켱 Ȯ϶ 
MYSQL TODO   (http://www.mysql.com/Manual_split/manual_Todo.html). 
̰  ֽ  TODO ̴. η F [TODO] .

5.2.1 Sub-selects
 Mysql ۵ ʴ´:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

Mysql  INSERT ... SELECT ... and  REPLACE ... SELECT ...  Ѵ. 
 -select  3.23.0 Ƹ    ̴.  ״  IN() 
   ִ.

5.2.2 SELECT INTO TABLE
Mysql   SELECT ...  INTO TABLE  ....   ʴ´. ,  Mysql  
SELECT ... INTO OUTFILE ...,  ϸ ⺻δ ϴ.

5.2.3 Ʈ(Transactions)
Ʈ   ʴ´. Mysql   atomic(ڼ?)  ۷̼   ̸ 
atomic ۷̼ rollback  Ʈǰ ϴ.  atomic ۷̼ ϸ 
insert/select/  ׷     嵵 浹   ֵ 
ش.  ƿ Ϲ ѹ(rollback)  ʿ.  LOCK TABLES   
UNLOCK TABLES   ̿Ͽ ٸ  尡 浹ϴ      ִ. 7.23 
[Lock Tables] .

5.2.4  ν Ʈ
 ν  ϵǰ   ִ SQL  Ʈ̴. ̷  
Ǹ Ŭ̾Ʈ ü Ǹ ٽ  ʿ䰡    ν   
. ̷   Ǵ  ѹ ؼǰ  Ŭ̾Ʈ  ְ޾ƾ ϴ 
Ͱ پǷ ӵ ȴ.     ̺귯 μ 
 ܰ踦 ų   ִ. (???  You can also  raise the conceptual  level by  having 
libraries of functions in the server.) 

ƮŴ Ư ̺Ʈ ߻    ν̴.    Ʈ 
̺ ڵ尡 ǰ  Ʈ   ϴ ̺   ִ 
 ν ġ  ִ.

δ  ν   ƮŴ ƴϴ. ƮŴ ʿ  
쿡   ־ Ϲ ӵ .

  ν ϰ    Mysql ߰   η F 
.(The TODO)

(**  Ʈ ó Ʈ  ͺ̽ ӵ Ͻŵϴ. Mysql
 ̷ ӵ  ĥ  ִ κ Ͽ   ӵ  . ̷
 κ ڱⰡ ϴ ͺ̽ 󸶳 ߿Ѱ Ǵ  ƾ  Դ
.   DBMS ȸ   ʴ 찡 .   
߻ , ȸ   Ѵ. ׷Ƿ ڰ ͺ̽  
 纻 غϸ,  ߻ϸ ۾ ٽ ؾ Ѵ. ƮŰ  ڷ 
Ἲ ϱ  ʿ ̴. **)

5.2.5 ܷŰ(Foreign Keys)
SQL  ܷŰ ̺      κ  Ἲ Ȯ 
 Ѵ. SELECT   ̺ ڷḦ  ϸ ̺ ؼ 
ó  ִ.

SELECT * from table1,table2 where table1.id = table2.id

7.12 [JOIN] .

Mysql ܷŰ(FOREIGN KEY)  ٸ SQL ǰ CREATE TABLE ɰ ȣ
ȯ    Ѵ: ܷŰ   ƹ͵   ʴ´.  ON  DELETE  ...     
FOREIGN KEY  κ    Ѵ. Ϻ ODBC  ø̼ 
̰ ڵ WHERE     ̴. ׷ ̰ κ ()
ϰ Ѿ . ܷŰ δ  üũ(constraint check)   
 üũ Ͱ ̺ Ȯ   ʿϴ. Mysql Ϻ 
̼ǿ ܷŰ ϴ  ʿ ϱ ( ۵ϵ  ϵ 
) ϴ  ̴.

Mysql ܷŰ  ̺ ڵ带    ø̼ǿ  DELETE 
 ߰Ͽ ON DELETE ...  Ǵ   ν  ذ ִ. 
 ̷ ϴ  ܷŰ ϴ Ͱ  (  쿡  ) 
Ⱑ  .

 Ͼȿ 츮 ܷŰ  Ȯ ̴. ׷ ּ mysqldump ODBC
  ǰ ˻  ֵ  ̴.


5.2.5.1 ܷŰ  ʴ 

ܷŰ   𿡼 ؾ  𸣴   ִ:

- Foreign key Ȳ ſ ϰ . ֳϸ, foreing key ǰ database
ܾ ϰ, foreign key ϴ  "ڿ" File (data file ű, 
ϰ, ϴ ...) Ѵ.
(** ش :  ̻ѵ ܷŰ   Ἲ Ģ    
   ϰ ȴ. ̰ ϰ ִ ϴ. **)

- INSERT  UPDATE  ӵ    ģ. ׸ ̷   ùٸ 
 ùٸ ̺ ڵ带 ϱ  κ  ܷŰ üũ  ʿ 
. 

-   ü ͺ̽  ۿ ϱ  ̺ Ʈ  
 ſ  ̺  ؾ Ѵ.  ̺  ڵ带 ϰ 
 ٸ ̺ ڵ带 ϴ  ξ . 

- Table  , (backup̳ ο sourceκ)  record ٽ ϴ
  Table   .

- Foreign key Ѵٸ, table dump(backup)ϰ ( dump ڷḦ) restoreϴ 
־  Ϸ  ϰ Ѿ Ѵ.

-  table ǰ ְ ϴ, ( Table ȣϰ ȴٸ) ܼ
 createδ  Ұ circular definition(ȯ)  ߻Ѵ.
(: A Table B ڷḦ ϴ foreign key  ְ,
         B C  foreign key, C A  foreign key 㵵 table ȴٸ
         ѹ A,B,C table   . A,B,C   foreign key 
         ִ   ȴ.)

ܷŰ     . ODBC  Ư ٸ Ŭ̾Ʈ α׷ 
 ̺ Ǿ ִ   ְ  ̾׷ µ ϸ ø
̼ µ  ̴. 
(:   foreign key ؼ ſ ̴. , ̰ foreign key 
         Ϻ ϻ̴. ٵ client  Table DATA 迡 ؼ 
         Ծ ؼ  Ű澲 ʰ ϰ, ׻ ڷ Ἲ(ռ[?]) 
         Ѵٴ  ſ ߿ϴ.

         Ư, Table 1~20 ƴ 100 Ѿٸ, client  Ű澲
         programingϴ ͵ , debugging 󺸴 .
          table 踦  ȭѴٸ programmer foreign key ޱ
         ٴ  Table checkؾ Ǵ  Եȴ.

         foreign key ǵǴ ֵ  table Data ϴ   ʱ
         ̴.  ǹ  ߳ û óϴ  ־
         foreign keyŭ 彺  . )

Mysql  ܷŰ Ǹ   ֵ ؼ Ŭ̾Ʈ    
 ؼ ϰ    ֵ  ̴.   '.frm'  
  ̰  ϰ ִ. 


5.2.6 

Mysql 並  ʴ´. ׷ TODO(  ) ִ.
MySQL doesn't support views, but this is on the TODO. 

5.2.7 `--'  ּ

Ϻ ٸ SQL ͺ̽ '--'   ּ Ѵ. mysql     '--' 
ϴ     Mysql  '#' ּ   Ѵ.  ڴ 
 C   Ÿ /*  this is  a comment  */   mysql    ִ.  7.28 
[Comment] .

Mysql '--'    ̴;  '--'   ּ · ڵ  Ǵ 
SQL ǿ   ߻Ų.   . 츮 ڵ payment 
!payment!   Էϵ ϰ ִ : 

UPDATE tbl_name SET credit=credit-!payment!

payment       ̶ ϴ°?

1--1 մ SQḺ  '--' ּ  ǹϴ   ̴.

'--' ּ ϴ ؽƮ  SQL α׷ ٸ   ؾ Ѵ:

shell> replace " --" " #" < text-file-with-funny-comments.sql \
         | mysql database

instead of the normal(  ???): 

shell> mysql database < text-file-with-funny-comments.sql

 Ϸ '--' ּ '#' ּ ٲ  ִ:

shell> replace " --" " #" -- text-file-with-funny-comments.sql

   :

shell> replace " #" " --" -- text-file-with-funny-comments.sql

(** Ϻ SQL ϴ --  ּ    Ƿ  MYSQL # 
ּ Ѵٴ ̴ **)

5.3 Mysql  ִ ǥ ΰ?

Entry level SQL92. ODBC level 0-2. 


5.4 BLOB  TEXT Ÿ 
BLOB  TEXT ʵ忡 GROUP BY  ORDER BY ϱ ϸ  ʵ带  
 ü  Ѵ. ̷  ϴ ǥ  SUBSTRING   ϴ 
̴.  :

mysql> select comment from tbl_name order by SUBSTRING(comment,20);

̷       ù ° max_sort_lengths  (⺻=1024) 
.

BLOB  TEXT  ⺻      NULL ÷ ̴.

BLOB and  TEXT cannot   have DEFAULT values   and will also  always  be NULL 
columns. 

5.5 COMMIT-ROLLBACK   ġ  ?
Mysql COMMIT-ROLLBACK   ʴ´.  COMMIT-ROLLBACK ȿ
 ٷ ؼ Mysql  ϴ Ͱ  ٸ ̺ 谡 ʿ
ϴٴ ̴. Mysql  ̺ ڵ  Ŭϴ ߰    
ũ   ִ  ʿϴ. ̷   纸 mysql 2-4  
. Mysql κ  ٸ SQL ͺ̽  ξ  .  ( ּ 
2-3 ) ̷  Mysql COMMIT-ROLLBACK  ̴.

а 츮   SQL       Űµ    ַ ̴.  κ 
COMMIT-ROLLBACK   ʿ  幰.  ̷ ϴ   
    ִ.

Ϲ Ʈ ʿ Ʈ LOCK TABLES  ڵ带 ©   ִ.  
ڵ带 Ʈ  Ŀ  ʿ䰡 .

츮 Ʈǰ Ŀ TODO ־  켱   ƴϴ.  ̷  
Ѵٸ CREATE TABLE  ɼ  ̴. ̰ ɼ  ̺
 ۵ϸ  ̺   ̶  ǹѴ.

츮   100%      ͺٴ          ͺ̽   ʿϴ. 
COMMIT-ROLLBACK  ϴ ӵ ջ ٸ  츮 װ  
̴. а  ߿ϰ ؾ ϵ   ִ. 츮  Ϳ 켱  ΰ 
ִ TODO .  ܰ  ޴  ̰ ٲ   켱
   ִ.

   ROLLBACK ̴. ѹ LOCK TABLES ̿Ͽ  
 COMMIT   ִ. ѹ ϱ   Mysql Ʈ    
ڵ带 ϰ ѹ ̳    ư  ֵ ٲپ Ѵ. 
  ̷    ʴ.( isamlog  ̷ 츦    
) ׷ ALTER/DROP/CREATE TABLE ѹ ϴ  ô ƴ.

ѹ  ϱ      ִ:

1. ϱ ϴ  ̺  . LOCK TABLES ...  
2.  ׽Ʈ(Test conditions)
3.    ȴٸ Ʈ Ѵ.
4. UNLOCK TABLES 

Ϲ  ѹ ̿ Ʈ ϴ ͺٴ ̷  ξ  
. ׷ ׻ 밡  ƴϴ. ̷  ذ    Ȳ
 Ʈ  带 ׿ ̴. ̷     ȴ. ׷
 Ʈ Ϻδ   ̴.

  ۷̼ǿ ڵ带 Ʈϴ    ִ.  ũ 
ϸ ſ ȿ ø̼   ִ:

-   õǾ ִ ʵ带 
-  ȭ  ʵ带 Ʈ

 ,    Ʈ    ٲ ͸ Ʈ Ѵ. ׸ 
For example, when we are doing updates on some customer information, we update only 
the customer data that have  changed and test only  that none of the changed  data, or 
data that depend on the changed data, have changed compared to the original row. 
ȭ  ׽Ʈ UPDATE   WHRE  Ͽ    ִ. ڵ尡 
Ʈ ʾҴٸ Ŭ̾Ʈ   ޽ ش:  " ٲ  
ΰ ٸ ڿ   ٲϴ". ׷ 츮  쿡  ڵ 
 ڵ带 Ͽ ش. ׷  ڴ     ڵ带  
  ִ. 

̷ ϸ "÷ ŷ" ϴ. ׷ δ  .  ֳϸ   
õǾ ִ  ÷ Ʈϱ ̴.  ̷  Ʈ  
 ̴:

UPDATE tablename SET pay_back=pay_back+'relative change';

UPDATE customer
  SET
    customer_date='current_date',
    address='new address',
    phone='new phone',
    money_he_owes_us=money_he_owes_us+'new_money'
  WHERE
    customer_id=id AND address='old address' AND phone='old phone';

    ̷ ϸ  ſ  ȿ̸   ٸ   Ŭ̾Ʈ pay_back   ̳ 
money_he_owes_us ÷  ٲپ   ۵Ѵ.

κ , ڴ ̺  (identifiers) ϱ  ѹ  ̺ 
 ϰ ;Ѵ. ̰  AUTO_INCREMENT ÷ SQL  LAST_INSERT_ID()  
, Ǵ mysql_insert_id()  C API  Ͽ   ȿ   ִ.  
18.4.49 [mysql_insert_id()] .

TcX  ̷  ذ   ֱ   low-level   ʿ  
ʴ´.  쿡  ο- ʿϴ. ׷ ̷  ؼҼ̴. ο
-  ϸ ̺ ÷ ÷   ִ.  :

UPDATE tbl_name SET row_flag=1 WHERE id=ID;

 row ߰ߵǰ row_flag   row ̹ 1 ƴ϶    row 
ڷμ 1 ȯѴ.

MySQL returns 1 fro the number of  affected rows if the row was found  and row_flag 
wasn't already 1 in the original row.

6. Mysql   ý

mysql   ǥ / ý  ִ. ̹ 忡 ̰ 
 ۵ϴ ϰ ִ.

6.1  ý̶ ΰ?
Mysql  ý ֿ  ͺ̽ select, insert, update, delete 
 ȣƮ  ̸  ̴.

߰ ɿ ͸   ɰ LOAD DATA INFILE   ۷̼ǰ  my
sql  Ư  ϴ κ ԵǾ ִ.


Mysql   ϴ  ̸ н  ̸(α ̸)̳ 
  ̸  谡 ٴ ! . κ mysql Ŭ̾Ʈ m
ysql  ̸  н  ̸ Ͽ Ϸ  ̴. ׷
 ̰  Ǹ ؼ̴. Ŭ̾Ʈ α׷ -u  --user ɼ  
 ̸ Ѵ. ̰ mysql  ̸ йȣ   ͺ
 ȿ    ִٴ  ǹѴ.  ̸ Ͽ  Ϸ
 ϴ   ̸ йȣ Ǿ  ʴٸ ӿ  ̴.

н  ̸ Ϲ 8ڷ ѵǾ ִ Ͱ ٸ mysql  ̸ 
16ڱ   ִ.

mysql йȣ н йȣ ƹ  . н ӽſ α  
ϴ йȣ ͺ̽   ϴ йȣ   .  m
ysql н α μ ϴ Ͱ ٸ ˰ йȣ ȣȭ
.


6.2 mysql  ϱ

mysql Ŭ̾Ʈ α׷ Ϲ  з(Ű ) ʿϴ.:  
ȣƮ,  ̸, йȣ.   mysql Ŭ̾Ʈ     
. ( ڴ [ ]  ݴ´)

shell>; mysql [-h host_name] [-u user_name] [-pyour_pass]


-p ڿ  йȣ ̿  ٴ  .

-h, -u, -p ü  ִ δ --host=host_name, --user=user_name and --passw
ord=your_pass   ִ.

mysql Ŀǵ ο  Ű   ⺻  Ѵ. ⺻ ȣƮ
̸ localhost ̰ ⺻  ̸ н α ̸̴.(-p   
йȣ  ʴ´) ׷  н  ̸ joe    
ϴ.:

shell> mysql -h localhost -u joe
shell> mysql -h localhost
shell> mysql -u joe
shell> mysql


ٸ mysql Ŭ̾Ʈ ϰ ۵Ѵ.

н ýۿ      ִ ⺻  ־ Ŭ̾Ʈ α׷ 
  ࿡ ɼ  ʾƵ ȴ:

 Ȩ 丮 '.my.cnf'   [client]  ǿ     ִ. 
Ͽ ̿    :
[client]
host=host_name
user=user_name
password=your_pass

4.14.4 [option files] .

 ȯ  Ͽ     ִ. ȣƮ MYSQL_HOST    
ִ. Mysql   ̸ USER, LOGNAME, Ǵ LOGIN   ִ. (̷  
̹ н α ̸ Ǿ  ̴. ׷Ƿ ٲ ʴ° ) й
ȣ MYSQL_PWD   ִ.(׷ ̰  ʴ;   )

     Ǿٸ ࿡    ϰ ȯ 
  ͺ 켱 .     ȯ  켱 
.

6. 2. 1 йȣ  

ٸ ڰ ߰  ְ йȣ ϴ   ʴ´. Ŭ̾Ʈ 
α׷   йȣ ϴ  Ʒ    赵  
Ͽ:

 ࿡ -pyour_pass Ǵ --password=your_pass ɼ .     
 ̴. йȣ ý Ȳ α׷(ps )    ֱ  ٸ 
ڰ    ִ.(mysql Ŭ̾Ʈ Ϲ ʱȭǴ   
ڸ 0 . ׷    ִ ª ƴ  ִ)

 -p Ǵ --password ɼ (йȣ   ). ̷  Ŭ̾Ʈ 
α׷ ͹̳ο йȣ :
shell> mysql -u user_name -p
Enter password: ********

Ŭ̾Ʈ йȣ ĥ  ͹̳ο '*'  ڸ ش. ׷Ƿ ٸ ڰ 
йȣ   . ٸ ڰ   Ƿ ࿡ йȣ Էϴ 
 ξ  ϴ. ׷   ȭ ũƮ Ŭ̾Ʈ α׷ 
ϸ  ʴ.

  Ͽ йȣ .   Ȩ 丮 '.my.cnf' Ͽ [client] 
ǿ йȣ   ִ.
[client]
password=your_pass

йȣ '.my.cnf' Ͽ Ѵٸ   ׷̳ ٸ ڰ б/⸦ 
   ؾ Ѵ.  ۹̼ 400 ̳ 600  Ȯ. 

4.14.4 [ɼ ] .

 йȣ MYSQL_PWD ȯ    ִ. ׷    
 ؼ ȵȴ. Ϻ ps α׷  μ ȯ溯 ִ ɼ 
ִ; MYSQL_PWD  ϸ ٸ   йȣ   ִ. ̷  p
s  ý μ ȯ溯 ˻  ִ  ٰ ϴ 
  ϴ.

߿    Ŭ̾Ʈ α׷ йȣ 䱸ϰų ϰ 
  '.my.cnf' Ͽ йȣ ϴ ̴.

6.3 mysql ϴ 

Ѱ õ  mysql ͺ̽(ͺ̽ ̸ mysql ) user, db, hos
t, table_priv, columns_priv  ̺ ȴ. mysql   , ׸ ȯ 
 (6.7 [ ] )  ̺  оδ.

mysql ϴ    ϴ ̸ Ʒ .̺ ÷ ̸ 
grant tables     Ǵ context Ǿ ִ.


Privilege              Column         Context
()          (÷)         (ȯ)
select         Select_priv     tables
insert         Insert_priv     tables
update         Update_priv     tables
delete         Delete_priv     tables
index          Index_priv      tables
alter          Alter_priv      tables
create         Create_priv     databases, tables or indexes
drop            Drop_priv       databases or tables
grant          Grant_priv      databases or tables
reload         Reload_priv     server administration
shutdown       Shutdown_priv   server administration
process        Process_priv    server administration
file            File_priv               file access on server


select, insert, update, delete  ͺ̽ ̺ ڵ忡  ۷
̼   ֵ Ѵ.

SELECT    ̺ (ڵ)   select  ʿϴ. 
 ͺ̽     ϴ Ư SELECT    
 ִ.      mysql Ŭ̾Ʈ   ִ:

mysql> SELECT 1+1;
mysql> SELECT PI()*2;

index(ε)  ε ϰų   ִ.

alter  ALTER TABLE    ֵ Ѵ.

create   drop  ο ͺ̽ ̺ ϰų ϴ ͺ
 ̺   ֵ Ѵ.

ڿ mysql ͺ̽ drop  ϸ,  ڴ mysql ٱ 
  ͺ̽   ִٴ°! .


grant  ڰ  ִ  ٸ ڰ   ֵ Ѵ.

file  LOAD DATA INFILE and SELECT ... INTO OUTFILE   ̿Ͽ   
ϰ   ִ  Ѵ. ̷   ڴ mysql  а 
  ִ  а   ִ  ȴ.

 ѵ  ۷̼ǿ Ǹ mysqladmin α׷  Ѵ. 
Ʒ ̺   ѿ    ִ mysqladmin  ش:

Privilege              Commands permitted to privilege holders
()          (ѿ  Ǵ )
reload         reload, refresh, flush-privileges, flush-hosts, flush-logs, flush-tab
les
shutdown       shutdown
process        processlist, kill


reload   grant ̺ ٽ о δ. refresh    ̺
  α   ݴ´. flush-privileges  reload ɰ Ǿ̴. ٸ 
flush-*  refresh    Ѵ. ׷     
쿡   ϴ.   α ϸ ݰ ٽ  Ѵٸ flush-logs  
refresh   ̴.
(** flush ɼδ ȣƮ, α ,  , ̺, status variables  
 ִ. SQL  Ǵ mysqladmin ƿƼ ϸ ȴ. **)

shutdown   ˴ٿѴ. (** ̰  ¾~~?? **)

processlist   ǰ ִ 忡   ش. kill  
 带 δ.  ڽ  ų    ٸ ڿ  
۵  μ  ־ ų   ִ.

  ɽ ؾ Ѵ:y:

 grant()  ڰ ٸ     ֵ Ѵ. ٸ 
Ѱ grant    ڴ    ִ.
 file     б   дµ    .... SELEC
T   ̿   ִ ...
The file privilege can be abused to read any world-readable file on the server into a 
database table, the contents of which can then be accessed using SELECT.
(**    ʾƵ ̿  ִ    ʴ° ٴ ̰ 
**)
 shutdown  ٸ ڿ  񽺸  ϵ   ִ.
  process  йȣ ϰ ٲٴ Ǹ   ϰ ִ Ǹ 
µ   ִ.
 mysql ͺ̽   йȣ ٸ    ٲٴ   
 ִ. (йȣ ȣȭǾ Ǿٰ ϴ,    ִ 
ڴ ٸ йȣ ٲ  ִ)

mysql  ý ٷ     ִ:
  ź ڸ ϰ   . ֳϸ ڿ  źϴ 
 ϰ ų   ̴.
 ڰ ͺ̽ ̺    ִ     
ͺ̽ ü       .
(** ׷ϱ create  drop  ָ ͺ̽ ü    . 
 ̺    ֵ  Ѵٴ  **)

{{}}
6.4  ý ۵ 

mysql  ý  ڰ  ͸ŭ   ֵ Ѵ. mysql  
 ,  Ȯ  ȣƮ ڰ   ̸  ȴ. 
ý  Ȯΰ  ѿ   Ѵ.


mysql ڸ Ȯϴµ ȣƮ̸  ̸ Ѵ Ѵ. ֳĸ ͳݿ 
̸ ٰ  ڶ    ̴.   whitehouse.gov 
ϴ  bill  microsoft.com ϴ  bill    ʿ 
. mysql   ̸  ִ ȣƮ ̿ ڸ Ѵ : wh
itehouse.gov ϴ bill Ư    ְ microsoft.com 
ϴ bill ٸ    ִ.

mysql   ΰ ܰ谡 ִ:

ܰ 1:  ڰ   ִ  Ǵ

ܰ 2 ( ڰ  Ǿ ) : ڰ Ϸ ɿ  
  ִ  û  Ǵ. , ͺ̽ ̺ sel
ect rows Ҷ, Ǵ ͺ̽ ̺    ̺  sele
ct  ִ ͺ̽    ִ Ȯ Ѵ.



     ܰ迡 mysql ͺ̽ user, db, host ̺ ̿
.grant ̺ ʵ Ʒ :

Table name     user            db              host
Scope fields   Host            Host            Host
(ʵ )     User            Db              Db
                Password                User
Privilege fields      Select_priv     Select_priv     Select_priv
( ʵ)     Insert_priv     Insert_priv     Insert_priv
                Update_priv     Update_priv     Update_priv
                Delete_priv     Delete_priv     Delete_priv
                Index_priv      Index_priv      Index_priv
                Alter_priv      Alter_priv      Alter_priv
                Create_priv     Create_priv     Create_priv
                Drop_priv       Drop_priv       Drop_priv
                Grant_priv      Grant_priv      Grant_priv
                Reload_priv             
                Shutdown_priv           
                Process_priv            
                File_priv               


  ι° ܰ踦 (û ), û ̺  ̶ ߰ 
tables_priv  columns_priv ̺ Ѵ.  ̺ ʵ  :

Table name     tables_priv     columns_priv
Scope fields   Host            Host
                Db              Db
                User            User
                Table_name      Table_name
                                Column_name
Privilege fields      Table_priv      Type
                Column_priv     
Other fields   Timestamp       Timestamp
                Grantor 




 (grant) ̺ ʵ   ʵ Ǿ ִ.

ʵ  ̺  Ʈ  Ѵ. ٽ ϸ Ʈ Ǵ con
text(ȯ, )̴.  , Host  User  'thomas.loc.gov'  'bob'  user 
̺ Ʈ thomas.loc.gov ȣƮ bob  Ҷ   ϴµ 
ȴ.ϰ Host, User, db ʵ尪  'thomas.loc.gov', 'bob', 'reports'  db 
̺ Ʈ thomas.loc.gov ȣƮ bob  reports ͺ̽   
ȴ. tables_priv  columns_priv ̺ ̺̳  Ʈ   ִ 
/÷  Ű  ʵ带 ϰ ִ.

 üũ ϱ , HOst  񱳴 ҹڸ  ʴ´. User, Password, Db, 
Table_name  ҹڸ Ѵ. mysql 3.22.12     Column_name  
ҹڸ  ʴ´. (3.22.11 ҹ )

 ʵ ̺ Ʈ εǴ  Ű ̴   ִ ䷹̼
.    Ϻϰ ϱ  پ (grant) ̺  
Ѵ. ⿡ ϴ Ģ 6.6 [Request access] .

 ʵ ڿ̸   ǵǾ; ⺻   ڿ̴:

Field name     Type    
Host            CHAR(60)        
User            CHAR(16)        
Password                CHAR(16)        
Db              CHAR(64)      (CHAR(60) for the tables_priv and columns_priv tables)



user, db, host ̺   ʵ ENUM('N','Y') ǵǾ ִ. --  
'N'  'Y'   ⺻ 'N' ̴.
(** ENUM Ÿ    ϳ  .ʵ Ÿ  **)

tables_priv  columns_priv ̺  ʵ SET ʵ ǵȴ:
(** SET Ÿ  ߿ 0̳ 1 ̻   **)

Table name     Field name     Possible set elements
tables_priv     Table_priv      'Select', 'Insert', 'Update',
                                'Delete', 'Create', 'Drop', 'Grant',
                                'References', 'Index', 'Alter'
tables_priv     Column_priv     'Select', 'Insert', 'Update',
                                'References'
columns_priv    Type            'Select', 'Insert', 'Update',
                                'References'

ϰ ؼ  (grant) ̺   Ѵ:

 user ̺ scope() ʵ  ῡ    ź 
 Ѵ.  ῡ Ͽ,  ʵ  ü (superuser)  
Ų.

 db  host ̺ Բ ȴ:
        - db ̺  ʵ  ȣƮ  ͺ̽   
ڰ   ִ Ѵ.  ʵ  ۷̼ Ǿ 
.
        - host ̺ db ̺ Ʈ  ȣƮ Ϸ   db 
̺ Ȯ  Ѵ. , ڰ  Ʈ  ȣƮ 
̽   ֵ Ϸ, "db" ̺ Ʈ Host  ΰ, "h
ost" ̺  ȣƮ Ʈ  ȴ.̷  6,6 [Request access] 
ڼϰ  ִ.

 tables_priv  columns_priv ̺ db ̺ ϴ. ׷   
  ִ:  ̺ ͺ̽ ܰ迡  ư ̺ ÷ ܰ迡 
  ִ.

 (reload, shutdown,Ÿ..)  user ̺    ִٴ  
! ֳĸ  ۷̼  ü  ۷̸̼ Ư ͺ
̽ ϴ  ƴϴ. ׷Ƿ ̷  ٸ (grant) ̺  
䰡 .,  user ̺  ۷̼   ִ  
   ȴ.

(file) ѵ  user ̺ Ѵ.    ƴϴ. 
׷  ȣƮ  аų   ִ  ϰ ִ ͺ̽ 
 ̴.

mysqld    (grant) ̺ ѹ д´.  ̺ ϰ ȿ
 Ϸ 6.7 [Privilege changes] .

 ̺    ϴ´  Ǿ Ȯϴ   
̴.   α׷ mysqlaccess ũƮμ Yves CArlier  mysql distrib
ution  ϰ ִ.  ۵ϰ ִ Ȯϱ  mysqlaccess  --help 
ɼ ־ غ.  6.11 [Access denied]  6.12 [Security] .


mysqlaccess  user, db, host ̺ Ѵ. ̺̳ ÷ ܰ ѱ
  ʴ´ٴ  .

6.5  , ܰ 1 :  Ȯ()

mysql  Ϸ     Ȯΰ йȣ   ϰų 
źѴ.  Ȯ ȵǸ    źѴ.  Ȯ Ǹ 
  ޾Ƶ̰ 2° ܰ  û ٸ.

 Ȯ ΰ  ϰ ִ:

 ϴ ȣƮ
 mysql  ̸

 Ȯ user ̺   ʵ(Host, User, Password) Ͽ 
.  user ̺ Ʈ ȣƮ̸  ̸ , йȣ Ȯ 
  ޾Ƶδ.

Ʒ  user ̺  ʵ尪   ִ:

 Host  ȣƮ ̸̳ IP  Ǵ  ȣƮ Ű 'localhost'   
̴.
 Host ʵ忡 '%'  '_'  ϵī ڸ   ִ.
 '%' Host   ȣƮ ̸ Ÿ.  ȣƮ  '%' . Ư 
ȣƮ  ̷      ִٴ  .
 ϵī ڴ User ʵ忡  ʴ´. ׷   شϴ 
   ִ.  Ϸ Ͽ   ̸ ִٸ Ŭ̾Ʈ 
  ̸ ſ  ڴ ͸ , ̸  ڷμ ֵȴ.
 Password ʵ    ִ.̰ ƹ йȣ   ִٴ  
ǹϴ  ƴϸ ڴ йȣ  ʰ  ؾ Ѵٴ ǹ̴.

Ʒ ̺  û ϴ "user" ̺  Host, User   
Ǵ ִ ̴:

ȣƮ/  : Ͽ شϴ 
'thomas.loc.gov'/'fred' : thomas.loc.gov  ϴ fred
'thomas.loc.gov'/'' : thomas.loc.gov  ϴ  
'%'/'fred' :  ȣƮ ϴ fred
'%'/'' :  ȣƮ ϴ  
'%.loc.gov'/'fred' : loc.gov   ȣƮ ϴ fred
'x.y.%'/'fred' : x.y.net, x.y.com, x.y.edu  ϴ fred (̰ Ƹ 
  ̴)
'144.155.166.177'/'fred' : 144.155.166.177 IP ּҿ ϴ fred
'144.155.166.%'/'fred' : 144.155.166 Ŭ C   ȣƮ ϴ fred


Host ʵ忡 IP ϵ ī带   ֱ (  '144.155.166.%'  
  ȣƮ ȴ) 144.155.166.somewhere   ȣƮ ̸ ̿Ͽ 
δϰ ̿ ɼ   ִ. ̷    mysql ڿ Ʈ(.)
 ϴ ȣƮ̸  ʴ´. 1.2.foo.com   ȣƮ ̷ ȣ
Ʈ̸ (grant) ̺ Host ÷ ġ ʴ´. IP ڸ IP ϵ ī 
 ġų  ִ.

 Ѱ ̻ user table  ִٸ   user table ұ? ̷ 
쿡 user table    ذ ϸ ,   Ҷ  
. user table  ٰ غ:


+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ...
| %         | jeffrey  | ...
| localhost | root     | ...
| localhost |          | ...
+-----------+----------+-



 ̺  ,  Ưϰ   ִ ȣƮ  Ѵ. 
(Host ÷ '%' " ȣƮ" ǹϿ ּѵ ϴ ̴) Ͽ ȣ
Ʈ   Ưϰ  ڰ ִ ͺ Ѵ.( Ǿ 
 User  " " ǹϿ ּѵ ϴ ̴.) ̷ ϸ  
user ̺  :



+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| localhost | root     | ...
| localhost |          | ...
| %         | jeffrey  | ...
| %         | root     | ...
+-----------+----------+-


   Ī ˰ Ǹ  ĪǴ  Ѵ. localhost
 jeffrey  ϷҶ, Host ÷ 'localhost'   Īȴ.  
 ̸   ϴ ȣƮӰ  ̸ Īȴ. ('%'/'jeffrey
'   Ī ȴ. ׷ ̺ ó ĪǴ  ƴϴ.)


ٸ  ִ. user ̺  ٰ غ:

+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| %              | jeffrey  | ...
| thomas.loc.gov |          | ...
+----------------+----------+-


 ̺  :

+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| thomas.loc.gov |          | ...
| %              | jeffrey  | ...
+----------------+----------+-

ù° thomas.loc.gov jeffrey ϴ  ĪǸ, whitehouse.gov  jef
frey ϴ  ι° Ī ȴ.

    , user ̺ Ͽ    ĪǴ  
 ϸ ȴ.


6.6  , 2ܰ : û 

{{}}Ǿٸ  2ܰ .  Ǿ   䱸  ڰ 
Ϸ   Ͽ  ڰ    ִ Ѵ. 
⼭  ̺  ʵ尡 ۵Ѵ.  user, db, host, table_priv, columns
_priv ̺  Ѵ. GRANT  REVOKE  ̿Ͽ  ̺ ٷ  
ִ. 7.25 [GRANT] . ( Ҵ   ̺ ʵ  ϴ  
  ̴; 6.4[Privilege] .)

user ̺   ڿ ü  ϸ  Ͱ  
ʹ  .  , user ̺ ڿ delete  ߴٸ 
 ȣƮ  ͺ̽ ڵ   ִ! ٸ ؼ user 
̺   ̸, ( ͺ̽  )Ը user 
̺   ϴ  . ٸ ڿԴ user ̺  'N' 
ϰ, db host ̺ Ͽ Ư ͺ̽  ѽ ϴ° 
.

db  host ̺ Ư ͺ̽  Ѵ.  ̺ Host  Db ʵ
 ϵī  '%'  '_'      ̸ ʵ (scope fie
lds)   Ѵ. '%' Host  " ȣƮ" ǹѴ. db ̺ Ho
st  ̸ "host ̺  ڼ  ϶" ǹ̴. A '%' or bla
nk Db value in the host table means or "any database." (** or   𸣰
׿. host ̺ Db   '%' Ǵ ̸ " ͺ̽" ǹѴٴ 
  **) User  ̸ ͸ ڷ ֵȴ.

   db  host ̺ а  Ѵ.(ÿ user ̺ д´.) 
db ̺ Host, Db, User  ʵ  ϸ host ̺ Host, Db  
ʵ  Ѵ. user ̺  Ưϰ Ǿ ִ   ǰ 
ѵ   ߿ ȴ.  ĪǴ  ã,   ߰
  Ѵ.

 tables_priv  columns_priv ̺ Ư ̺ ÷ õ  Ѵ.d
b host ̺ Host ʵ  ϵī带 Host ʵ忡   ִ. ׷
 Db, Table_name, Column_name ʵ忡 ϵī峪 鰪   .

Host ̺ ϵī带    tables_priv  columns_priv ̺ 
db ̺ ϰ  Ǹ  ϴ. 

û   Ʒ Ѵ. - ҽ ڵ忡 ģϴٸ, ⼭ ϴ 
 ڵ忡  ˰ ణ ٸٴ    ִ.⼭  ڵ
  ۵ϴ İ ϴ.   ϰ ϴµ ̰ ִ ̴.

 û ؼ(shutdown, reload )   user ̺ üũ Ѵ. ֳ
 user ̺   ϱ ̴. Ͽ û  ϸ 
 Ǹ ƴ 쿡  źεȴ. , mysqladmin shutdown 
 ϴµ user ̺ Ͽ ڿ shutdown   , db h
ost ̺ üũ ʴ  źεȴ. (̷ ̺ Shutdown_priv ÷
   ̷  ʿ䵵 )

ͺ̽ õ û (insert, update )   user ̺ Ͽ 
 ü()  Ѵ. Ͽ û  ϸ  
ȴ.

user ̺ ü  ϸ,  db  host ̺ Ͽ 
ͺ̽ õ  Ѵ:

1.  db ̺ ĪǴ Host, Db, User ʵ带 ã´. Ϸ  ȣ
Ʈ ̸ Mysql  ̸ Host  User ĪǴ. ڰ ϱ ϴ 
ͺ̽ Db ʵ忡 Īȴ.  Host  User    źεȴ.

2. ĪǴ db ̺  ְ Host ʵ尡  ƴϸ,   ͺ
̽   Ѵ.


3. ĪǴ db ̺  Host ʵ尡 ̸, host ̺  ȣƮ 
ͺ̽   ִ ǴѴٴ  ǹѴ. ̷ ,  ڼ  
 host ̺ ĪǴ Host  Db ʵ带 ã´. host ̺ ĪǴ  
  źεȴ. ĪǴ    ͺ̽   db 
 host ̺ Ͽ  intersection Ͽ ȴ.(** insertection  
ϸ . and  **) ٽ ؼ, db host ̺   'Y' Ǿ  
 ȴ.̷  db ̺ Ϲ    , ׷
 host ̺   host  Ͽ     
.)

db  host ̺  ̿ ͺ̽ õ    ,  
̷  user ̺  ü ѿ ߰Ѵ.   û 
 ϸ  ȴ. ٸ ,  tables_priv  columns_priv ̺
  ̺ ÷  ϰ  ѿ ߰Ѵ.    
 ǰų źεȴ.

  ü  Ʈ ѿ ͺ̽, ̺, ÷ õ  
߰ϴ Ȯ ʴ.... ̷    ʱ⿡ û 꿡 Ͽ 
ϴ... (It may not be apparent why the server adds the database-, table- and col
umn-specific privileges to the global user entry privileges for those cases in which 
the user privileges are initially found to be insufficient for the requested operatio
n.) û Ѱ  ̻  ʿϱ ̴.  , INSERT ... SELECT 
   insert  select    ʿϴ.   user ̺ 
Ѱ  ϰ db ̺ Ʈ ٸ   ̴. ̷ , 
ڴ ̷ û ϱ  ʿ   ִ. ׷  ü
 ٸ ̺ ؼ .....(In this case, you have the necessary privileges to 
perform the request, but the server cannot tell that from either table by itself;) ; 
 Ʈ  ε  յǾ Ѵ.

host ̺ ""   ϴµ   ִ. TcX, host ̺
  Ʈ  ý ԵǾ ִ. ⼭   ȴ.

 ʴ ȣƮ Ű  host ̺   ִ. ϴٰ 
 ʴ   ġ public.your.domain ý ִٰ غ. ڴ 
 Ʈ  ȣƮ   , host ̺ Ʈ   ý
 Ѵ : 

+--------------------+----+-
| Host               | Db | ...
+--------------------+----+-
| public.your.domain | %  | ... (all privileges set to 'N')
| %.your.domain      | %  | ... (all privileges set to 'Y')
+--------------------+----+-

翬   ϴ´ Ǿ ִ   ̺  ׽ؾ 
. (  mysqlaccess  ) 



6.7    


mysqld   ,   ̺  ޸𸮷 ö󰡰 ̶ ȿϰ 
.

GRANT, REVOKE, SET PASSWORD  ̿  ̺  ϸ ٷ  ν 
Ѵ.

 ̺  ߴٸ(INSERT, UPDATE  Ͽ),   ̺ 
簡ϵ ϱ  FLUSH PRIVIEGES ̳ mysqladmin flush-privileges  ؾ 
Ѵ.׷    ٽ ϱ     ʴ´.

  ̺ Ǿٴ   , ̹ ϴ Ŭ̾Ʈ  
   ޴´:

1. ̺ ÷   Ŭ̾Ʈ  û ȴ.
2. ͺ̽    USE db_name ɺ ȴ.
3. ü Ѱ йȣ  Ŭ̾Ʈ    ȴ.
{{}}

6.8 ʱ mysql Ѽ

mysql ġϰ , mysql_install_db ũƮ ؼ ʱ   ؾ 
Ѵ. 4.7.1 [Quick install] . mysql_install_db ũƮ mysqld  ϰ, 
   ̺  ʱȭѴ:

- mysql root ڴ ̸     ִ.  ȣƮ   
ִ. 
 : ó root йȣ ִ. ׷  йȣ root  
   ι޴´.

- ͸ ڴ 'test'  'test_'  ϴ ͺ̽    ι
´.  ڰ  ȣƮ    ͸ ڷ ֵȴ.

- ٸ  źεȴ.   Ϲ ڴ mysqladmin shutdown ̳ mysqladmin p
rocesslist    .


ġ  ʱ  а  Ǿ ֱ    mysql root  
йȣ ؾ Ѵ.   ϸ ȴ. (PASSWORD() Լ ̿ йȣ
 ؾ Ѵ!):

shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
           WHERE user='root';
mysql> FLUSH PRIVILEGES;

or

shell> mysqladmin -u root password new_password
(** PASSWORD()  Լ  ʾƵ ǹǷ .  SQL grant  
̿    **)

ù°  ϸ  user ̺ йȣ ƮѴ. ̰  ٽ 
 ̺ е ؾ Ѵ.(FLUSH PRIVILEGES ). ֳϸ ٸ δ 
 ˸   ̴.

(**  ̺ ٽ  ʾƼ  ߴ йȣ  ȵǴ 찡 
 Դϴ.  ϰ ־ؿ **)

root йȣ Ǿ  root Ҷ йȣ ؾ Ѵ.

߰ ¾ ϰų ׽Ʈ  йȣ  ʿ䰡   root йȣ
  ܵΰ  ̴. ׷  ۾ ϱ  ݵ йȣ 
ߴ Ȯؾ Ѵ.

⺻  ϴ mysql_install_db ũƮ 캸. ٸ ڿ 
   ̰ ⺻   ִ.

  Ͱ ٸ ʱ  ϱ ϸ, mysql_install_db ũƮ 
ϱ  ϸ ȴ.

ϰ  ̺ ٽ   mysql ͺ̽ ϴ 丮 '*IS
M'  '*.ISD'  ؾ Ѵ. ( 丮 database 丮 'mysq''̶
 ̸ پִ. mysqld --help ؼ database 丮    ִ.) ϴ
    mysql_install_db ũƮ .


6.9 mysql ο   ߰ϱ

ΰ  ڸ ߰  ִ : GRANT   Ǵ mysql  ̺  
. GRANT  ϴ   ȣǴ ̴.

Ʒ  ο ڸ ϱ   mysql Ŭ̾Ʈ ϴ 
ش.    ߴͰ  ⺻   ϴ  
. ̰  ٲٱ  mysqld ǰ ִ  ýۿ ־ Ѵٴ  
Ѵ. (**ʱⰪ localhost  ϹǷ**)  mysql root ڷ 
ؾ ϰ root ڴ mysql ͺ̽  insert Ѱ reload   
־ Ѵ. root  йȣ ٲپ, Ʒ  mysql  ¿ 
йȣ ؾ Ѵ.

GRANT  ̿ ο ڸ ߰  ִ:

shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
           IDENTIFIED BY 'something' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
           IDENTIFIED BY 'something' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;

 GRANT    ڸ Ѵ:

monty :      ִ   йȣ ؾ 
Ѵ. 츮 monty@localhost  monty@"%"  GRANT  ؼ ݵ Ǹ 
 Ѵ. localhost  ߰ , mysql_install_db    localhost 
 ͸  (?)  ȣƮ Ҷ 켱 ´. ֳϸ 
 Host ʵ       ̴. (**  ̺  
 Ư Host  ͺ ϴ  . 

admin : йȣ  localhost    reload process   
ι . ̰ ڰ mysqladmin processlist Ӹ ƴ϶ mysqladmin reloa
d, mysqladmin refresh, mysqladmin flush-*    ִ.ͺ̽ õ 
 ε ʾҴ. ̰ ߰ GRANT   ߿   ִ.

dummy : йȣ     localhost   .  
(privilege type) USAGE ̱  ü  'N' Ǿ ִ. USAGE 
 ƹ ѵ  ʴ´. ߿ ͺ̽ õ    ִ.

     INSERT    ߰   ̰쿡 
  ̺ ٽ е ˷־ Ѵ.(**FLUSH PRIVILEGES **)

shell> mysql --user=root mysql
mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('something'),
                'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('something'),
                'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysql> INSERT INTO user SET Host='localhost',User='admin', 
                 Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
                        VALUES('localhost','dummy',");
mysql> FLUSH PRIVILEGES;

mysql    'Y'  ٸ  ִٴ  . 3.22.11  Ŀ 
  ִ Ȯ INSERT  ⼭ admin ڿ Ǿ.

 ϱ  ʵ带 'Y'  user ̺ ϸ  ȴٴ  
. db  host ̺  ʿ. (**   db host ̺ 
  . db   ִ ͺ̽  ϰ ϰ host ̺
 db̺   ϰ ϱ  ʿ ̴.    user 
̺ õǾִ **)

 INSERT (dummy ) user ̺  ÷ Ȯϰ  ʾ
. ֳĸ  ÷ ⺻ 'N' Ǿ ֱ ̴.

  custom ̶ ڸ ߰Ѵ. custom localhost, server.domain, 
whitehouse.gov   ִ. localhost bankaccount ͺ̽ 
   whitehouse.gov expenses ͺ̽,   ȣƮ󿡼 cus
tomer ͺ̽ ϱ Ѵ.   ȣƮ󿡼 stupid йȣ 
ϱ Ѵ.

GRANT  ̿ ̷   ϱ    :

shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
           ON bankaccount.*
           TO custom@localhost
           IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
           ON expenses.*
           TO custom@whitehouse.gov
           IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
           ON customer.*
           TO custom@'%'
           IDENTIFIED BY 'stupid';

 ̺     Ϸ   . ( 
FLUSH PRIVILEGES  ؾ Ѵٴ  ):

shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
       VALUES('localhost','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
       VALUES('server.domain','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
       VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
mysql> INSERT INTO db
       (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
        Create_priv,Drop_priv)
       VALUES
       ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
       (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
        Create_priv,Drop_priv)
       VALUES
       ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
       (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
        Create_priv,Drop_priv)
       VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~

ó  INSERT  custom ڰ йȣ Ͽ پ ȣƮ 
  ֵ user ̺  ߰Ѵ. ׷ ׿  ۹̼ǵ  
´. (  ⺻ 'N' ̴)   INSERT   ȣƮ 
  , custom  bankaccount, expenses, customer ͺ̽   
ϴ db ̺  ߰Ѵ. Ϲ  ̺  Ͽ, 
  ϱ    ̺ ٽ е  ־ Ѵ.

Ư ڰ Ư  ýۿ   ֵ ϰ Ѵٸ,  
 GRANT    ִ:

mysql> GRANT ...
           ON *.*
           TO myusername@"%.mydomainname.com"
           IDENTIFIED BY 'mypassword';

 ̺  Ϸ   Ѵ:

mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername', 
           PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;
~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~

 ̺ ٷ  xmysqladmin, mysql_webadmin, xmysql α׷   
. http://www.mysql.com/Contrib  ̷ ƿƼ ã  ִ.


6.10 йȣ  

  ߿ Ģ ش : INSERT  UPDATE   ƴ йȣ 
  ݵ ȣȭϱ  PASSWORD() Լ ؾ Ѵ!! user ̺ 
ȣ ÷ؽƮ(**Ϲ ؽƮ **) ƴ϶ ȣȭ · ϱ ̴. 
̷  ؾ   йȣ Ϸ  ̴:

shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
       VALUES('%','jeffrey','bLa81m0');
mysql> FLUSH PRIVILEGES;

÷ؽƮ  'bLa81m0'  user ̺ йȣ  ȴ.jeffrey 
  йȣ   Ϸ   mysql Ŭ̾Ʈ  йȣ ȣ
ȭؼ    .  ȣȭ йȣ('bLa81m0' ƴϴ) user 
̺ йȣ(÷ؽƮ 'bLa81m0' ̴) Ѵ. 񱳴 ϰ  
 źѴ:

shell> mysql -u jeffrey -pbLa81m0 test
Access denied

йȣ user ̺ Էµ  ݵ ȣȭǾ ϱ , INSERT  
  ؾ Ѵ:

mysql> INSERT INTO user (Host,User,Password)
       VALUES('%','jeffrey',PASSWORD('bLa81m0'));

 SET PASSWORD    PASSWORD() Լ ؾ Ѵ:

mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('bLa81m0');

 : PASSWORD() Լ йȣ ȣȭ Ѵ. ׷ н йȣ 
ȣȭϴ  ٸ. н йȣ mysql йȣ   PASSWORD() 
 н йȣ (** /etc/passwd  **) ȣȭǾ   ٰ 
ϸ  ȴ.

GRANT ... IDENTIFIED BY ̳ mysqladmin password   йȣ ϸ 
PASSWORD() Լ ʿ. Ѵ йȣ ȣȭؼ Ѵ: 

mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'bLa81m0';
shell> mysqladmin -u jeffrey password bLa81m0

(** , GRANT ̳ mysqladmin password  ϴ° ϰ? mysql ȣ
ȭ ˰ н ٸ н  mysql ڴ  ٸٴ ͵ ٽ 
ѹ ϰ ־ մϴ.**)



6.11  ź   

mysql  Ϸ    ź  , Ʒ ϴ Ϳ  ذ 
 ã  ִ: 

- ʱ  ̺  ϱ  mysql ġ  mysql_install_db ũƮ 
Ͽ°?  ʾҴٸ ũƮ . 6.8 [Default privileges] . 
  ̿ ʱ     ִ:

        shell> mysql -u root test

     ̴. mysql ͺ̽ 丮 'user.ISD' 
 ִ Ȯغƾ Ѵ. (Ϲ 'mysql ġ 丮/var/mysql/user.IDS' 
)

- ġ ϰ  ,  ϰ ڿ   ؾ Ѵ:

        shell> mysql -u root mysql

ʱ⿡ mysql root ڸ йȣ      ִ. ȹ 
ֱ , ٸ mysql ڸ ϱ   root йȣ ؾ Ѵ.
root Ϸϴµ   ٰ :

        Access denied for user: '@unknown' to database mysql

̰ user ̺ User ÷ = root   , mysqld  Ŭ̾Ʈ 
ȣƮ̸ ؼ  ٴ  ǹѴ. ̷  --skip-grant-tables ɼ 
  ٽ ؾ ϰ '/etc/hosts'  ϰų '\windows\hosts' Ͽ 
 ȣƮ  ߰ؾ Ѵ.

- 3.22.11   3.22.11̳   Ʈߴٸ, mysql_fix_privilege
_tables ũƮ ߴ°?  ʾҴٸ . mysql 3.22.11 GRANT  
 鼭  ̺  ٲ.

- (INSERT  UPDATE  )  ̺  ư ȭ  ݿ  
 ̸, FLUSH PRIVILEGES  ϰų mysqladmin flush-privileges  
   ̺ ٽ е ؾ Ѵٴ  .׷   
ϱ  ȭ  ݿ ʴ´. root йȣ ϰ   
flush ϱ йȣ  ʿ䰡 . ֳĸ   йȣ ٲپ
 𸣱 ̴. 

-  ߰    ̸  ٲپ ̴.  ̺ 
ϴ  ο Ŭ̾Ʈ ӿ  ġ ̹ ϰ ִ  6.
7 [Privileges changes] Ѵ  ģ.

- ϱ , mysqld   --skip-grant-tables ɼ ־ . ׷ 
mysql  ̺   ְ Ȱ ϴ´ ۵ϴ üũϴ mysqla
ccess ũƮ   ִ. ϴ´  Ǿ mysqld  ο  
̺  ֵ mysq1admin flush-priveleges  Ѵ.
 : ̺ εϴ  --skip-grant-tables ɼ ȿȭѴ. ̸  
 ٿŰ ٽ  ʰ  ̺   ִ.

- , Python, ODBC α׷ ϴµ  ִٸ, mysql -u user_name db_name  
Ǵ mysql -u user_name -pyour_pass db_name    õغ. (-p  
йȣ̿  ٴ  .  --password=your_pass ·ε  
 ִ) mysql Ŭ̾Ʈ  Ǹ α׷  ִ ̸  ѿ 
 .

- йȣ  ۵ , INSERT, UPDATE, SET PASSWORD  йȣ 
ϸ鼭 PASSWORD() Լ ݵ ؾ Ѵٴ  . PASSWORD() Լ 
GRANT ... INDENTIFIED BY ̳ mysqladmin password  ߴٸ ʿϴ. 6.1
0 [Passwords] .

- localhost   ȣƮ ̸  ̴.  ȣƮ ϰ   
 Ŭ̾Ʈ Ϸ ȣƮ ⺻̴. ׷ MIT-pthreads ϴ 
ۿ localhost   ۵ ʴ´. (localhost  н 
  . ׷ MIT-pthreads н   ʴ´.) ̿ 
 ýۿ  Ϸ,  ȣƮ ̸ Ȯϰ ֱ  --host 
ɼ ؾ Ѵ. ׷ mysqld  TCP/IP  . ̰,  ȣƮ
 user ̺ Ͽ  ȣƮ̸ ־ Ѵ. (  ȣƮ Ŭ
Ʈ α׷ Ѵٰ ϴ ̴.)

- mysql -u user_name db_name  ͺ̽ Ϸ    ź  
, user ̺   ̴. mysql -u root mysql  Ͽ ϰ  
SQL  :

        mysql> SELECT * FROM user;

⼭  ȣƮ̸ mysql  ̸ ´ Host  User ÷  
Ǿ ־ Ѵ.

- Access denied  ޽ Ϸ ڿ ȣƮ ̸, ׸ йȣ 
ߴ θ  ̴. Ϲ user ̺  ޽  ȣƮ 
̸  ̸ Ȯϰ ´   ־ Ѵ.


- ٸ ýۿ mysql      ޽ , user ̺ 
 Ϸ ϴ ȣƮ ̸ ٴ  Ѵ:

        Host ... is not allowed to connect to this MySQL server

( ȣƮ!)  ƿƼ mysql Ͽ user ̺ ϰ ϴ 
/ȣƮ ̸ ߰Ͽ ذ  ִ. mysql 3.22  ϰ  ʰ 
 ϴ ý IP ڳ ȣƮ ̸ 𸥴ٸ, user ̺ Host ÷  
'%'  Էϰ  ýۿ --log ɼ  mysqld  . Ŭ
Ʈ ýۿ  õ  mysql α׿    ߴ  
 ִ. (׷ '%' α׿   ȣƮ ̸ ٲ۴. ׷ 
 ȿ    ִ.)

- mysql -u root test  ۵ ϴµ mysql -h your_hostname -u root test  ٿ 
 , user ̺ Ȯ ȣƮ ̸  ̴. Ϲ  user 
̺ Host    ȣƮ ̸(**   ȣƮ ̸  
**)   ִµ ý  ؼ ƾ FQDN(fully-qualified domain name - ** 
  ̸ ȣƮ ̸ **) óϴ ̴(Ǵ Ųٷ ؼ).
 , user ̺ ȣƮ ̸ 'taejun'  Ǿִµ, DNS mysql ȣƮ 
̸ 'taejun.subnet.se' ˷   ̰  ۵  ̴. 
user ̺ Host ÷ μ شϴ IP ڳ ȣƮ ̸ ߰. (, us
er ̺ Host  ϵī ڸ   ִ.  , 'taejun.%'. ׷
 ȣƮ̸  '%' ϴ     ʴ´!)

- mysql -u user_name test  ۵ϴµ mysql -u user_name other_db_name  ۵ 
ʴ´ٸ db ̺ other_db_name   ̴.

-  ýۿ mysql -u user_name db_name  ۵ ϴµ, ٸ Ŭ̾Ʈ ý
 mysql -u user_name db_name  ۵  ʴ´ٸ, user ̺̳ db ̺ 
Ŭ̾Ʈ ý   ̴.

-  Access denied   ذ ϸ, user ̺ ϵī('%' Ǵ '
_') ϰ ִ Host     . ſ Ϲ  Host='%
' ׸ User='some user' Է ϰ , ̷ ϸ  ýۿ   l
ocalhost   ֵ Ѵٰ ϴ ̴. ̰  ۵ ʴ 
 ⺻ ѿ Host='localhost'  User=''  ԵǾ ֱ ̴. Host 
 '%'   и 'localhost'  ֱ , localhost   ο 
Ϻ   ȴ. Ȯ  ι° ׸ Host='localhost'  User='so
me_user'  Էϰų Host='localhost'  User=''  ϴ ̴.

-    :

        Access to database denied

db  host ̺   ̴.  db ̺ Ͽ Host ÷ 
ٸ, host ̺ db ̺ Ͽ Ǵ ȣƮ ̸ ִ Ȯؾ Ѵ.
(** Ϲ db ̺ host  δ , host ̺ ϴ ȣƮ 
  ִ **)

-    :


        Access to database denied

SELECT ... INTO OUTFILE Ǵ LOAD DATA INFILE  SQL  ϴ , user ̺ 
Ͽ file  Ǿ  ʾ ̴.

- ٸ   Ͽ , mysqld   ɼ . ( , 
--debug=d,general,query) ׷  ɿ   Բ,  õϴ ȣ
Ʈ ڿ    ̴. G.1 [Debugging] .

- mysql  ̺ ٸ  εư   ϸƮ ˷߰ڴٰ 
, mysql  ̺ Ͽ ؾ Ѵ. mysqldump mysql   
   ִ.  mysqlbug ƮƮ Ͽ  ø.

-    , mysqld   ʰų ߸ ̳ Ʈ 
Ϸ õϴ :

        Can't connect to local MySQL server
        Can't connect to MySQL server on some_hostname

 mysqld   ۵ϴ ps ̿ ȮѴ.   ִ Ȯ
   ƾ Ѵ.(Ϲ `/tmp/mysql.sock' ) Ǵ telnet host_name 3306 
  õغ.  ڼ   mysqladmin version  mysqladmin -h hos
t_name version     ִ.    ִ mysql Ÿ 丮 
 α  غ.

{{}}{{}}{{}}- Ŭ̾Ʈ α׷  ̳ ȯ    з͸   
ִٴ  . ࿡  ʾҴµ Ŭ̾Ʈ ߸ ⺻  з
͸   Ǹ, Ȩ 丮 ȯ溯 '.my.cnf'  غ
.  ⼭   зͿʹ 谡  ý  mysql  
    ִ. 4.14.4 [Option files] . Ŭ̾Ʈ ƹ ɼǵ 
 ʾҴµ Access denied  ޽  , ɼ  ߿  йȣ
  ʾҴ Ȯ . 4.14.4 [Option files] .




6.12 ũĿ Ͽ mysql ϰ ϴ 

mysql    Ϲ йȣ ؾ Ѵ. йȣ   ܼ
 ؽƮ ۵ ʴ´.

/Ŭ̾Ʈ  ȣȭ ʴ´;      ִ   
 ִ ؽƮ ۵ȴ.     Ǹ  ư ϱ   
(mysql 3.22 ̻ )   ִ.   Ȯϰ ϱ  ssh 
ġ  ִ. (http://www.cs.hut.fi/ssh ) ̰ ̿ mysql  Ŭ̾Ʈ 
̿ ȣȭ TCP/IP    ִ.

mysql ý  ϰ    ϰ :

-  mysql ڰ йȣ .  ڰ йȣ  'mysql - u 
̸'  ̿ ϰ  ڷ α  ִٴ  . ̰ Ŭ
̾Ʈ/ ø̼ Ϲ ۵̴. mysql_install_db ũƮ 
ϱ   ũƮ Ͽ   йȣ ٲ  ִ. Ǵ mysql ro
ot  йȣ ٲ    ϸ ȴ:

shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
           WHERE user='root';
mysql> FLUSH PRIVILEGES;

- mysql  н root ڷ  . mysqld  ٸ ڰ  
 ִ.   ϰ ϱ  mysql̶ н ڸ   ִ. 
ٸ н ڷ mysqld ϸ user ̺ root  ̸ ٲ ʿ䰡 
. mysqld ٸ н ڰ ϱ  mysql.server ũƮ ϸ 
. Ϲ su  Ѵ.  ڼ  16.7 [Changing mysql user] .

- mysqld   ִ ڸ ͺ̽ 丮 б/   
ִ Ȯ.

-  ڿ process   . mysqladmin processlist  ϸ  
ϴ     ִ. ׷Ƿ ̷    ִ ڴ 
ٸ  UPDATE user SET password=PASSWORD(_'no_secure') Ǹ   ִ.mysql
 process   ڸ  ߰(extra)  Ѵ.׷ mysql roo
t ڴ  Ϲ  Ǿ αϰ    ִ.

-  ڿ file   . ̷  ִ ڴ mysqld  
 ִ  ý      ִ.  ϰ ϱ  
SELECT ... INTO OUTFILE  Ǵ    ڰ б⸸    
 ϴ    .
(** file  LOAD DATA INFILE , SELECT .. INTO OUTFILE  ̿Ͽ   
ϰ   ִ  Ѵ. ̷   ڴ mysql  а 
  ִ  а   ִ  ȴ. Ϲ ڿ ̷   
 . ʿ κи  ִ  .  븻. **)

- DNS  ŷ Ѵٸ  Ը ȣƮ̸  IP . ⺻ 
mysqld  --secure ɼ ȣƮ̸ ϰ Ѵ.   ϵī ڰ 
Ե ȣƮ̸  Ҷ ſ ؾ Ѵ.

- mysql.server ũƮ н root  йȣ ִ´ٸ,  ũƮ 
 root   ֵ ؾ Ѵ.

 mysqld ɼ Ȱ õǾ ִ:

--secure : gethostbyname() ý ݿ  ϵ IP ڰ  ȣƮ̸ reso
lve  Ͱ  Ѵ. ̰   ٸ ȣƮ ̸ ķؼ 
ϴ  ư .  ɼ  ȣƮ̸    ߰
Ѵ. ؼϴµ δ ð  ɷ mysql 3.21 ⺻  Ǿ 
 ʴ. mysql 3.22 ȣƮ̸ ĳϰ  ɼ ⺻ Ǿ ִ.
(** Լ gethostbyname() ȣƮ ̸ ڷ ޾ ׿ شϴ IP ּ  Ÿ 
 شϴ ü   ü ͸ ϴ ԼԴϴ.  ؼ ȣ
Ʈ ̸  ش IP ּҸ ã.**)

--skip-grant-tables :  ɼ ϸ   ý   ʴ´. 
׷  ڰ  ͺ̽   ִ! (mysqladmin reload  
    ̺ ϵ   ִ.)

--skip-name-resolve : ȣƮ̸ ؼ ʴ´.  ̺  Host ÷ 
ݵ IP ̰ų ȣƮ̾ Ѵ.

--skip-networking : Ʈ  TCP/IP  . mysqld   н 
   .  ɼ MIT-pthreads ϴ ýۿ  
۵  ʴ´. ֳĸ MIT-pthreads Ű н   ʱ 
. (**  ϴ 鿡Դ ? н   ϴϱ. 
̿ ϰ postgres 6.3 ĺΰ? ⺻ н   
. **)
9. mysql  (functions)

9.1 mysql ϴ 
mysqld  ޽   Ÿ  ֽϴ. : Czech, Dutch, English(⺻
), Ÿ  (** Ÿ  Ŵ . ѱ, Ϻ   ⼭ ֽ
. 9.1.1  ϼ. **)

mysqld Ư  Ϸ --language=lang Ǵ -L lang ɼ ϸ ˴
.

> # mysqld--language=swedish

Ǵ 

# mysqld --language=/usr/local/share/swedish

  ҹڷ ǥմϴ.

  (⺻) 'mysql_base_dir/share/LANGUAGE/' ֽϴ.

 ޽  ƮϷ 'errmsg.txt'   ϰ 'errmsg.sys'  
    մϴ.

# comp_err errmsg.txt errmsg.sys

 mysql ο  ׷̵ϸ ο 'errmsg.txt' Ϸ   
 ľ մϴ.


9.1.1 Ϳ  ϴ  
⺻ mysql ISO-8859-1 (Latin 1)   մϴ.  ڼ ̱ 
 ϴ  Դϴ.

ڼ ̸  ڼ   ִ ׸ SELECT  ORDER BY  
GROUP BY     ĵǴ մϴ.

Ҷ configure --with-charset=charset ɼ Ͽ ڼ ٲ  ֽ
ϴ. ڼ  4.7.1  ϼ.
(** ѱ ϱ ؼ  κ ߿մϴ. 츮  Ҷ ڼ sjis 
پ ָ ˴ϴ. sjis Ϻ 2Ʈ   Դϴ. ̷ ؾ   ǰ 
ǥ   ֽϴ. **)  


9.1.2 ο ڼ ߰
**  **


9.1.3 ƼƮ  
Ƽ ̽ ڼ  _MB ũθ   ֽϴ.

**  **


9.2 Ʈ α
mysqld   --log-update=file_name ɼ  ϸ mysqld Ÿ 
Ʈ   sql   ϴ α   մϴ.    data 丮(**   
mysql ġ 丮  data  丮 **) ϵǸ file_name.#  · ˴ϴ. # 
mysqladmin refresh  mysqladmin flush -logs, FLUSH LOGS , Ǵ  
 ˴ϴ.

--log  -l ɼ ϸ  ̸ 'hostname.log' Ǹ, restart refreshes  
 ο α   ʽϴ. ⺻ mysql.server ũƮ  -l ɼ
 mysql  մϴ. production enviroment(?) Ͽ Ҷ   
 ʿϸ mysql.server -l ɼ   ֽϴ.

Ʈ α״ ̼  Ÿ Ʈ  α  ϴ. ׷
 WHERE  UPDATE DELETE شϴ ڵ带 ã ϸ α 
  ʽϴ.  ̹ ϴ  Ҷ Update  õ˴ϴ.

Ʈ α Ͽ ͺ̽  ƮϷ    ϸ ˴ϴ. (α 
 'file_name.#' ¶ )

# ls -1 -t -r file_name.[0-9]* | xargs cat | mysql

ls Ȯ   α    մϴ.

̰ ͺ̽ ջ    Ϸ   ϸ,  ջ 
 ð ̿ Ͼ Ʈ ٽ Ҷ(redo)   ֽϴ.

 ٸ ȣƮ ̷ ͺ̽     ͺ̽   
ȭ Ҷ Ʈ α׸   ֽϴ.

9.3 mysql ̺ ִ ũ
mysql ü ̺ ִ ũⰡ 4G̸  ý    ũ   
ֽϴ.   2G Դϴ. ֶ󸮽 2.5.1 4G̸,  ֶ󸮽 2.6 
1000G  Դϴ.  ̺ ũ  4G̰ų, (MYSQL )  ý 
Դϴ. 4G ̻ Ȯϱ   mysql  ٲ Դϴ. η F ϼ
.

Ŵ ̺ б  ϸ   ̺ ϳ   ϴ pack_isam  
  ֽϴ. pack_isam Ϲ ̺ ּ 50%  Ͽ ȿ  
ū ̺   ֽϴ. 12.3 [pack_isam] ϼ.

ٸ ַ MERGE ̺귯 ԵǾ   identical ̺  ϳ 
  ֽϴ. (⼭  Identical  ̺ identical  ÷  ٴ 
  ǹմϴ.)    MERGE  ε    ʱ    ̺  
(collection) ˻ϴµ   ֽϴ.   ϳ ⿡ ε ߰
 Դϴ.

(**  б  ̺ mysql ̼ ϰ     ֽ
ϴ.  б⸸ ϴ     б  ̺    ϴ. 
**)
10. mysql ִ   

10.1  ũ 
mysqld  ϴ ⺻  ũ     ִ.

shell> mysqld --help

   mysqld ɼ ϰ   ش. µǴ  ⺻ 
ϰ   ϴ.

Possible variables for option --set-variable (-O) are:
back_log              current value: 5
connect_timeout       current value: 5
join_buffer           current value: 131072
key_buffer            current value: 1048540
long_query_time       current value: 10
max_allowed_packet    current value: 1048576
max_connections       current value: 90
max_connect_errors    current value: 10
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
net_buffer_length     current value: 16384
record_buffer         current value: 131072
sort_buffer           current value: 2097116
table_cache           current value: 64
tmp_table_size        current value: 1048576
thread_stack          current value: 131072
wait_timeout          current value: 28800

mysqld   ̸        ִ.

shell> mysqladmin variables

 ɼ ؿ Ѵ.   ũ, ,  ũ Ʈ̴. 'K'(ųιƮ) 
 'M'(ްƮ) տ ٿ    ִ.   16M 16 ްƮ 
Ų. ҹڴ  ʴ´. 16M  16m .

-back_log 
mysql   ִ ִ  û . ̰ main mysql 尡 ſ ª ð
 ſ   û    Ѵ. ̶  尡  üũϰ 
ο 带 ϴµ ణ ð ɸ.(׷  ª ð) back_log 
 mysql  ο û ϴ  ߱  ª ð 󸶳 
 û ׾Ƶΰ ִ Ѵ. ſ ª ð ſ   ɶ
   Ѿ Ѵ.

ٸ    tcp/ip  ޴ listen queue ũ̴.  ü ̷ ť
 ũ⿡ Ѱ谡 ִ. Unix system call listen(2) Ŵ ڼ  ִ. ba
ck_log Ѱ ü  Ȯغ. back_log ִ밪   ȿ 
. 

-connect_timeout 
Bad handshake ϱ   Ŷ mysql  ٸ ð.()

-join_buffer 
(ε  ʴ ) full-join ϴ  ũ. ۴  ̺ 
̿  full-join ѹ Ҵ ȴ. ε ߰    ۸ 
Ű full join ӵ ų  ִ. (Ϲ   ϴ   
ε ߰ϴ ̴)

-key_buffer 
ε  ۸ǰ  忡 Ѵ. Ű ۴ ε  ϴ 
 ũ̴. ε  ̺ delete insert ۾  ϸ Ű ۰ 
Ű  .   ӵ  LOCK TABLES . [Lock Tables] 
.

-max_allowed_packet 
 Ŷ ִ ũ. ޽ ۴ net_buffer_length Ʈ ʱȭ ʿϸ 
  Ŷ Ʈ ų  ִ.⺻ ū Ŷ ⿡ ۴. Ŵ BLOB 
÷ Ѵٸ  Ѿ Ѵ. ڰ ϴ ִ blobŭ ũ  ؾ Ѵ. 
 
-max_connections 
 Ŭ̾Ʈ . mysqld ʿϴ  (descriptor) ڸŭ  ÷
 Ѵ. ؿ  ũ ѿ   .

-max_connect_errors 
ȣƮ ִ  ̻ interrupted       ȣƮ 
blockȭȴ. FLUSH HOSTS  ȣƮ block   ִ.

-max_join_size 
ִ  ũ̻ ũ д  ϸ  .  ڰ where 
  ʰ ð  ɸ鼭 鸸 ڵ带 д  Ϸ ϸ 
  Ѵ.

-max_sort_length 
BLOB TEXT  Ҷ ϴ Ʈ . (   ù° max_sort
_length Ʈ ȴ.  õȴ)

-net_buffer_length 
ǿ  ۰ ʱȭǴ ũ. Ϲ ٲ  ſ  ޸𸮸 
   Ǵ ǿ °   ִ. (̰ Ŭ̾Ʈ   sql 
 ̴. ǹ  ũ⸦  ۴ ڵ max_allowed_packet Ʈ
 Ѵ) 

-record_buffer 
 ˻ ϴ  忡  ˻ ̺ Ҵϴ  ũ.  ˻
  ϸ   Ѿ Ѵ.

-sort_buffer 
 ʿ  忡 Ҵϴ  ũ. order by  group by ۷̼ 
 Ϸ   Ų. 16.4 [ӽ ] .

-table_cache 
 忡   ִ ̺ . mysqld ʿ ϴ  ũ 
ڸŭ   Ѷ. mysql    ̺ ΰ  ũͰ 
ʿϴ.  ũ   Ѵ. ̺ ĳ  ۵ϴ 10.6 [
̺ ĳ] Ѵ.
 
-tmp_table_size 
ӽ ̺    mysql "The Table tbl_name is full"̶  ޽ 
. ſ  group by Ǹ ϸ   Ѿ Ѵ.

-thread_stack 
   . creash-me test(** : ͺ̽ ġŷ ϴ ׽
ƮԴϴ. ״ ͺ̽ ׿)       ޷
. ⺻ Ϲ  ũ. 11 [ġũ] 

-wait_timeout 
   Ȱ(activity)  ٸ ð().

table_cache  max_connections    ִ ִ    ģ.  
 Ű ýۿ   ũ per-process  Ѱ 
  ִ. (** ... imposed by your operating system on the per-process number of
open file descriptors.  ̻ϹǷ  )
׷  ýۿ  Ѱ踦 ų ִ. ̷ Ϸ  ýۿ  Ѱ
 ȭŰ  ſ پϹǷ ü  ؾ Ѵ.

table_cache  max_connections  谡 ִ.   200   ּ 2
00 * n  ̺ ĳ  Ѵ. ⼭ n ο ̺ ִ ̴.

mysql ſ  ˰ ϱ  Ϲδ ſ  ޸𸮷 
   ޸𸮰      ȴ.

 ޸𸮿  ̺  ߰ Ŭ̾Ʈ ִ  Ѵ
   Ѵ.

shell> safe_mysqld -O key_buffer=16M -O table_cache=128 \
           -O sort_buffer=4M -O record_buffer=1M &

޸𸮰      Ѵ.

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
           -O record_buffer=100k &

Ǵ: 

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
           -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

ſ     mysqld  Ḷ ּ ޸𸮸 ϵ  
ʾҴٸ "swapping problems"   ̴.

mysqld ɼ ٲپ װ  شϴ νϽ  ģٴ 
 .

ɼ ٲپ ȿ     غ.

shell> mysqld -O key_buffer=32m --help

 --help ɼ   . ׷  Ŀǵ ο  
 ȿ ¿ ݿ  ̴. 


10.2 ޸   <޸ ȭ>

Ʒ ϴ  mysqld  ޸𸮸 ϴ  ؼ Ÿ 
. ޸  õ   ̸ ־.


- Ű ( key_buffer)  忡 Ѵ.  ϴ ٸ ۴ 
ʿѴ Ҵ ȴ.

-    帶 Ư  Ѵ. (64k,  thread_stack) ,  
( net_buffer_length), result  ( net_buffer_length) .  ۿ result 
۴ ʿҶ max_allowed_packet   ȴ. ǰ    
ǹ 繮  Ҵ ȴ. 
(** When a query is running a copy of the current query string is also alloced.) 

-    ⺻ ޸𸮸 Ѵ.
- ޸    ȵȴ. ( ̺ ϰ. ׷ ̰ ٸ ̾߱
) ֳϸ 4GB 32Ʈ ޸  κ  ̺  ũⰡ ʱ 
̴. 츮 64Ʈ ּ   ý    츮 ޸  
 Ϲ  ߰ ̴.

- ̺  ˻ ϴ  û read ۿ Ҵ ȴ. ( record_buff
er)

-   ѹ  Ǹ κ  ӽ ̺  ʰ  
ȴ. κ ̺ ޸ (HEAP) ̴̺. Ŵ  ڵ带 ų 
BLOB ÷  ӽ ̺ ũ  ȴ.   ޸  
̺ tmp_table_size ʰ "The table tbl_name is full"̶   
̴.  Ͼȿ ʿҶ ڵ ޸ (HEAP) ̺ ũ (NI
SAM) ̺ ٲٵ ĥ ̴. 
  ذϱ ؼ mysqld tmp_table_size ɼ Ͽ ӽ ̺ ũ⸦ 
̰ų Ŭ̾Ʈ α׷ SQL_BIG_TABLES sql ɼ Ͽ Ѵ. 7.
24 SET OPTION  . 
mysql 3.20 ӽ ̺ ִ ũ record_buffer*16̴. 3.20  ϰ 
ٸ record_buffer  Ѿ Ѵ.  mysqld   --big-tables ɼ 
Ͽ ׻ ӽ ̺ ũ     ӵ  ģ.

-  ϴ κ û  ۿ ϳ ΰ ӽ  ҴѴ. 16.4 
[ӽ ] Ѵ.
 
- κ ¡(parsing)   ޸𸮿 ̷.  ۿ ޸ o
verhead ʿ Ϲ  ޸ Ҵ(slow memory allocation) freeing(޸ 
) õȴ. ޸𸮴    Ŵ ڿ Ҵ ȴ.( mallloc()  
free() )

-  ε  ѹ   Ǵ 忡   ѹ 
.   帶 ̺ ,  ÷ ÷ , 3 * n   ũⰡ 
ȴ. ( n ִ ڵ ̸ BLOB ÷ ش ʴ´) BLOB BLOB 
 ̿ 5 8 Ʈ   Ѵ.

- BLOB ÷   ̺ ۴ Ŵ BLOB    ֵ  Ŀ
. ̺ ˻ϸ ۴ ִ BLOB ŭ ۰ Ҵ ȴ.

-   ̺ ̺ ڵ鷯 ĳ Ǹ FIFO  ȴ. Ϲ
 ĳ 64 Ʈ ´. ÿ ΰ  忡 ̺ ϸ ĳ
 ̺  Ʈ Ѵ. 10.6 [̺ ĳ] Ѵ.

- mysqladmin flush-tables   ʴ  ̺ ݰ  Ǵ 
尡     ̺ ݴ´ٰ ǥѴ. ̰ ȿ  
𸮸 Ѵ. 


ps  ٸ ý Ȳ α׷ mysqld  ޸𸮸 ϰ ִٰ  
. ̰ ٸ ޸ ּ -ö .   ֶ󸮽 ps  
û  ʴ ޸𸮸 ϴ ޸𸮷 Ѵ. ̰ swap -s ̿ 
밡  üũϿ ȮҼ ִ. 츮 mysqld  ޸   α
 ׽ؼ mysqld ޸  .


10.3 ӵ   ġ /ũ  <Ͻ ȭϱ>

 ׽Ʈ κ  mysql ġũ  Ǿ ٸ  ý
ۿ Ͻִ  ִ. 

static ũ Ҷ    ӵ   ִ. ͺ̽ ϱ 
 TCP/IPٴ н  ϸ      ִ. 

 pgcc -O6 ϸ  . 'sql_yacc.cc'  ɼ Ϸ 
gcc/pgcc      ޸𸮰 ʿϱ  180M ޸𸮰 ʿ
ϴ.  mysql Ҷ libstdc++ ̺귯  ʱ  CXX=gcc 
ؾ Ѵ.

- pgcc ϰ δ -O6 ɼ ϸ mysqld  gcc  ͺ
 11% .

-  ũϸ (-static  ʰ) 13% .
    If you connect using TCP/IP rather than Unix sockets, the result is 7.5% slower. 
- н  ϴ ͺ tcp/ip ϴ  7.5% .

- On a Sun sparcstation 10, gcc 2.7.3 is 13% faster than Sun Pro C++ 4.2. 
- On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads. 
(**   .  ϱ... **)

TcX  mysql   pgcc ϵǾ  ũǾ.


10.4 How MySQL uses indexes

prefix- and end-space compressed. See section 7.26 CREATE INDEX syntax (Compatibil
ity function). 

 ε(PRIMARY, UNIQUE and INDEX())  B-trees  ȴ. ڿ ڵ
   (?) ȴ. 7.26 [ε ] . 

ε  : 
- WHERE  شϴ ڵ   ã
-  Ҷ ٸ ̺ ڵ 
- Ư Ű MAX()  MIN()  ã
- ̳ ׷ȭҶ ε Ű ϸ ̺ ϰų ׷ȭѴ. Ű  DES
C   ε д´.
-  쿡  Ͽ  ʰ  ´.  ̺ ϴ  
÷ ̰ Ư Ű Ǿ  ӵ ε Ʈ    
ִ.

  .

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;


 ÷ ε col1  col2  شϴ ڵ带    ִ.  и
  ÷ ε col1  col2   ȭ  ε   ڵ
  Ȯϰ ڵ带    ε ϵ Ѵ.

̺  ÷ ε ٸ ȭⰡ ڵ带 ãµ  εŰ 
  ִ.    ÷ ε(col1, col2, col3) ٸ (col1), (col1,col2) 
(col1,col2,col3) ε Ͽ ˻   ִ.  

MySQL can't use a partial index if the columns don't form a leftmost prefix of the inde
x. 
Suppose you have the SELECT statements shown below: 
(** ؼ  ȵǴµ  ø      **)

mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If an index exists on (col1,col2,col3), only the first query shown above uses the index. 
The second and third queries do involve indexed columns, but (col2) and (col2,col3) are 
not leftmost prefixes of (col1,col2,col3). 

ε (col1,col2,col3) ִٸ    ù° Ǹ ε Ѵ. 
°  °  ε ÷ ԵǾ  (col2)  (col2,col3) (col1,col2,c
ol3) ε ش ʴ´.

MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant 
string that doesn't start with a wildcard character. For example, the following SELECT 
stat ements use indexes: 

mysql  LIKE μ ϵī ڷ  ʴ  ڿ̶ LIK
E 񱳹 ε Ѵ.    SELECT  ε Ѵ.

mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

ù° 忡 "Patrick" <= key_col < "Patricl"   ڵ常 ȴ. ι° 
忡 "Pat" <= key_col < "Pau"   ڵ常 ȴ. 


 SELECT  ε  ʴ´:

mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;

ù° 忡 LIKE  ϵī ڷ ϰ ִ. ι° 忡 LIKE 
  ƴϴ.



10.5 WHERE  ȭϱ
(̹    ϰ  ʴ. mysql  ȭ ִ.)

In general, when you want to make a slow SELECT ... WHERE faster, the first thing t
o check is whether or not you can add an index. All references between different tables 
should usually be done with indexes. You can use the EXPLAIN command to determine 
which indexes are used for a SELECT. See section 7.21 EXPLAIN syntax (Get informat
ion about a SELECT). 
Ϲ  SELECT ... WHERE   Ϸ   Ȯؾ   
 ߰ ̴. ٸ ̺̿  ۷(references ) Ϲ 
  ȴ. SELECT   ε ϴ ϱ  EXPLAI
N    ִ. 7.21 [Explain] .

mysql ϴ ȭ  .


- ʿ Ծ 

       ((a AND b) AND c OR (((a AND b) AND (c AND d))))
    -> (a AND b ANDc) OR (a AND b AND c AND d)

- (folding)

       (a<b AND b=c) AND a=5
    -> b>5 AND b=c AND a=5

-   (   ʿ)

       (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
    -> B=5 OR B=6

- ε Ǵ  ǥ ѹ ȴ.
 (Constant expressions used by indexes are evaluated only once.) 

- WHERE    ̺ COUNT(*) ̺    ´. 
 ̺  NOT NULL ǥ ̿  ȴ.  

- ȿ   ǥ ̸ ŵȴ. mysql Ұϰ شϴ ڵ尡  
SELECT   Ѵ.

- GROUP BY  ׷ (COUNT(), MIN() ...)   HAVING WHERE 
 .
(** HAVING  ε  . ׷Ƿ  HAVING  
ʴ° ӵ鿡  **) 

-   ο  WHERE  ϰ  ڵ带 ϵ 
 WHERE  .

- mysql Ϲ ּ ڵ带 ã  ε Ѵ. =, >, >=, <, <=, 
BETWEEN ׸  'something%' ó  ϵī  ʴ LIKE  
Ͽ 񱳸   ε Ѵ. (** 10.4  Ͽ like  Ҷ 
ϵī ϴ like  ϸ ε  ʴ´.  ܾθ 
ϴ ÷ ڷḦ ã   ̴. **)

- Any index that doesn't span all AND levels in the WHERE clause is not used to opti
mize the query. 

 WHERE  ε Ѵ.: 

... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimized like "index_part1='hello'" */

 WHERE  ε  ʴ´.: 

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* No index */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */

- ǿ ٸ ̺   ̺  д´.  ̺  .
         ̺̳ 1 ڵ常 ִ ̺
        WHERE  UNIQUE ε PRIMARY KEY  ϰ  ε
  ǥε ̺ 

         ̺  ̺ ȴ.

    mysql> SELECT * FROM t WHERE primary_key=1;
    mysql> SELECT * FROM t1,t2
               WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

-  ɼ õϿ ̺ ϴµ     ã´. (ORDER B
Y GROUP BY  ÷  ̺   Ҷ  ̺  
õȴ)

- ORDER BY  ٸ GROUP BY   , Ǵ ORDER BY  GROUP BY 
 ť ù° ̺ ƴ ٸ ̺ ÷ ϰ  ӻ ̺ 
.

-  ̺ ε ã ڵ 30%̸ ϴ (best) ε ȴ. ׷ 
ε   ̺ ˻ ȴ.

-  쿡 mysql   ȸ ʰ ε ڵ带   
. ε   ÷ ڶ Ǹ óϴµ  ε Ʈ 
Ѵ.

-  ڵ尡 µǱ  HAVING   ʴ ڵ ǳʶڴ.

 ſ   ̴:

mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
           WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

 Ŀ ε Ʈ Ͽ  Ѵ.(ε ÷ ڶ ):

mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name
           WHERE key_part1=val1 and key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

 Ǵ    ʰ      ε 
Ѵ:

mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...
mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...


10.6 ̺  ݴ 

open ̺ ĳ table_cache ִ밪 Ŀ  ִ. (⺻ 64 ;   mysql
d -0 table_cache=#  ٲ  ִ) ĳ  á, ׸ ٸ 尡 
   , Ǵ mysqladmin refresh  mysqladmin flush-tables Ҷ 
ϰ ̺   ʴ´.

̺ ĳ    ĳ Ʈ ϵ ϱ    
Ѵ.

-   ߴ    ʴ ̺ ݴ´.
- ĳ  á  ̺   ο ̺  Ѵٸ ĳ 
 ŭ ӽ Ȯȴ.
- ĳ ӽ Ȯ ̰ ̺    Ȳ  ̺ 
ݰ ĳ Ѵ.

̺  ú ٶ .  ̺ ϴ ΰ 尡 
ְų  ǿ ̺ ι ϸ(with AS) ̺ ι  Ѵٴ 
̴. ̺ ù°  ΰ  ũ͸ . ; ߰ ̺ 
 ϳ  ũ͸  ̴. ó 濡 ϴ ߰  
ũʹ ε Ͽ ȴ. ;  ũʹ  忡 ȴ.


10.6.1 ͺ̽   ̺ 鶧 

丮   ִٸ open, close ׸ create ۷̼  ̴. 
 ٸ  ̺ SELECT  ϸ ̺ ĳ    ణ overhea
d  ̴. ֳĸ  ̺ ִٸ ٸ ̺  ϱ ̴. 
̺ ĳ ũ ؼ ̷ 带   ִ.


10.7  ̺  

mysqladmin status       ̴:

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

 6̺ ߴµ ̷  Ȳ ̴.

mysql Ƽ带 Ѵ. ׷ ÿ  ̺  Ǹ   ִ. 
 Ͽ Ͽ ٸ Ȳ  ΰ 忡   ̱  ̺
  ú 帶  ȴ. ̰ Ÿ Ͽ ణ ޸
 ϳ ߰  ũ͸ Ѵ.  忡 ε  
.


10.8 ͺ̽ ̺ ɺ ũ 

ͺ̽ 丮 ̺ ͺ̽ ٸ ġ ű ο ġ 
 ũ   ִ. ̷ ϴ   찡 ִ.   ͺ̽
    Ͻý ű  .

mysql ̺ ɺ ũǾٴ  ϸ ɺ ũ Ű ̺ 
   ִ. realpath() call  ϴ  ýۿ ۵Ѵ. (ּ 
 ֶ󸮽 realpath() Ѵ) realpath()  ʴ ýۿ ÿ 
 ο ɺ ũ ο ϸ ȵȴ. ̷ 쿡 Ʈ Ŀ ̺
   ִ.

mysql ⺻ ͺ̽ ũ  ʴ´. ͺ̽ ɺ ũ
  ʴ ۵   ̴. mysql  丮 db1 ͺ̽ 
 db1 Ű db2 ɺ ũ ٰ غ:

shell> cd /path/to/datadir
shell> ln -s db1 db2

 db1 tbl_a ̺ ִٸ db2 tbl_a Ÿ ̴.  尡 db1.tbl
_a Ʈϰ ٸ 尡 db2.tbl_a Ʈϸ   ̴.

   ʿϸ , `mysys/mf_format.c'  ڵ带 ؾ Ѵ.: 

if (!lstat(to,&stat_buff))  /* Check if it's a symbolic link */
    if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))

 ڵ带   Ѵ : 

if (realpath(to,buff))


10.9 ̺  Ŵ 

mysql   deadlock-free ̴.  Ǹ Ҷ ѹ  ʿ  
ûϰ    ̺  ɾ Ѵ.

WRITE  ϴ   :

- ̺    ̺ write  Ǵ. 
- ̷ 찡 ƴ϶ write  ť  ûѴ.

READ  ϴ   :

- ̺ write    ̺ read  Ǵ.
- ̷ 찡 ƴ϶ read  ť  ûѴ.

 Ǿ   write  ť 忡    ׷  read  
ť 忡 Ѵ.

̺ Ʈ  ϸ SELECT   ̻ Ʈ   ٸ
ٴ  ǹѴ.

̷  ذϱ  ̺ INSERT  SELECT ۷̼  
 쿡   ϸ ȴ. ӽ ̺ ڵ带 Էϰ ѹ ӽ ̺
  ̺ ڵ带 ƮѴ.

  :

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> insert into real_table select * from insert_table;
mysql> delete from insert_table;
mysql> UNLOCK TABLES;

  쿡 SELECT 켱 ְ ʹٸ INSERT ɼǿ LOW_PRIORITY 
or HIGH_PRIORITY ɼ   ִ. 7.13 [Insert] . (** LOW_PRIORITY 
ϸ Ŭ̾Ʈ ̺    INSERT   ̷. **)

 ť ϱ  `mysys/thr_lock.c'  ŷ ڵ带 ٲ  ִ. ̷  writ
e  read   켱   ø̼ǿ   ִ.

10.10 ̺  ۰ 迭ϴ  <** ̺ ȭ **>

 ̺ ִǼ       ִ ũ̴:

-  NOT NULL ÷ Ѵ. ӵ   ÷ 1 Ʈ  
 ִ.
- default    ϴ. ԷµǴ  ⺻ ٸ  Ȯϰ  Էµ
. INSERT  ù° TIMESTAMP ÷̳ AUTO-INCREAMENT ÷  
Է ʿ䰡 . 18.4.49 [mysql_insert_id()] .
-  ̺ ۰    integer Ÿ .   MEDIUM
INT   INT  .
-   ÷ ٸ(VARCHAR, TEXT or BLOB columns),   ڵ 
 ȴ.   ӵ   (~) Ǵ   . 10.1
4 [Row format] .
- mysql Ǹ ȿ ȭϱ    ͸ Է isamchk --a
nalyze . ̷ ϸ      ڸ Ű  ε 
 ƮѴ. ( unique ε ׻ 1̴) 
- ε ε  Ÿ Ϸ 
isamchk --sort-index --sort-records=1  .(if you want to sort on index 1). 
ε  ĵ  ڵ带 б  unique ε ٸ ̷ ϴ 
 ӵ  ϴ   ̴.
- INSERT      .  SELECT  ξ 
. Ÿ ̺ Է  LOAD DATA INFILE .  INSERT  
ϴ ͺ  20 . 7.15 [Load] .

 ε  ̺ Ÿ ԷҶ   ϸ ӵ ų 
 ִ.
1. mysql̳ Perl  CREATE TABLE ̺ .
2. mysqladmin flush-tables . (**  ̺   **)
3. isamchk --keys-used=0 /path/to/db/tbl_name . ̺  ε  
Ѵ. 
4. LOAD DATA INFILE  ̿ ̺ Ÿ Է.
5. pack_isam  ְ ̺ ϱ ϸ pack_isam .
6. isamchk -r -q /path/to/db/tbl_name  ̿ ε ٽ .
7. mysqladmin flush-tables .

- LODA DATA INFILE  INSERT    ӵ  Ű ۸ Ų
. mysqld safe_mysqld -O key_buffer=# ɼ ϸ ȴ.   16M 
ǳ  ٸ Ǹ ̴.
- ٸ α׷ Ͽ Ÿ ؽƮ Ϸ Ҷ SELECT ... INTO OUTFIL
E  . 7.15 [LOAD DATA INFILE] .
-  ٷ insert update   LOCK TABLE Ͽ ̺  
 ӵ ų  ִ. LOAD DATA INFILE ׸ SELECT ...INTO OUTFILE 
 ̱  LOCK TABLE ϸ ȵȴ. 7.23 [LOCK TABLES/UNLOCK 
TABLES] . 

̺ 󸶳 ȭǾ Ϸ '.ISM' Ͽ isamchk -evi  Ѵ. 1
3 [Maintenance] .



10.11 INSERT  ӵ  ġ κ <** insert ȭ **>

insert ϴ ð   ȴ:

    Connect: (3) 
    Sending query to server: (2) 
    Parsing query: (2) 
    Inserting record: (1 x size of record) 
    Inserting indexes: (1 x indexes) 
    Close: (1) 

()  ð̴. ̰ ̺ Ҷ ʱ overhead ϰ 
 ʴ. ( ú Ǵ Ǹ ߻)

The size of the table slows down the insertion of indexes by N log N (B-trees).
 

̺ ũ N log N(B-trees)  ε Է . (**  ̻. 
̺ Ŀ  ε  ٴ ̰ **)
 
̺  ɰų insert     Ͽ Է ӵ    
ִ.    ϸ  insert  5  ӵ .

mysql> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql> INSERT INTO a VALUES (8,26),(6,29);
mysql> UNLOCK TABLES;

ֿ ӵ ̴  INSERT  Ϸǰ  Ŀ ѹ ε ۰  
 .   ٸ  INSERT    ε  ÷ 
 ̴.     Էϸ  ʿ.

ŷ    ׽Ʈ  ð   ִ. ׷  忡  
ð   ִ.(ֳĸ  ٸ ̴)
 :

thread 1 does 1000 inserts
thread 2, 3, and 4 does 1 insert
thread 5 does 1000 inserts

   2, ,3 4 1 5  ĥ ̴.  ϸ 2,3,4 Ƹ 1
̳ 5    ̴. ׷  ð 40% .

INSERT, UPDATE, DELETE ۷̼ mysql ſ . ׷  ٿ 
5  ̻ insert update    ߰ϸ      ִ. ٿ 
  ڷḦ ԷѴٸ ٸ 忡 ̺ ϵ ϱ  ( 100
0ٸ) UNLOCK TABLES ϴ LOCK TABLES ϸ ȴ. ̷ ϸ 
    ִ. (**  Է ϰ ߰  Ǯٰ ٽ  Ŵ  
ݺ**)

 LOAD DATA INFILE   .



10.12 DELETE  ӵ  ġ κ <** DELETE  ȭ **>

ڵ带 ϴ ð Ȯ ε ڿ Ѵ. ڵ带    
ε ĳ ũ⸦ ų  ִ. ⺻ ε ĳ 1M ̴;  ϱ 
 Ǿ Ѵ.( ޸𸮸  ִٸ 16M )


10.13 mysql ִ ӵ  

ġŷ ! mysql ġũ Ʈ  α׷   ִ. (Ϲ
 'sql-bench' 丮 ) ׸ Ը¿ ° . ̷ ϸ  
 ذ  ִ ٸ ذå ã   ſ   ذå ׽Ʈ 
 ִ.

- mysqld  ɼ . ޸𸮰  ӵ .
  10.1 [MySQL parameters] .
- SELECT  ӵ  ϱ  ε . 
10.4 [MySQL indexes] .
-  ȿ ÷ Ÿ ȭ.    NOT NULL ÷ 
. 10.10 [Table efficiency] .
- --skip-locking ɼSQL û  ŷ ش. ӵ   
  Ѵ:
         isamchk ̺ üũϰų ϱ  mysqladmin flush-tables  
 ̺ ÷ؾ Ѵ. (isamchk -d tbl_name  ȴ. ֳϸ ̰ 
 ̺  ֱ ̴)
         ÿ  ΰ mysql   ̺ ƮϷ Ѵٸ  
 Ͽ ΰ mysql   ȵȴ.

        --skip-locking ɼ MIT-pthreads Ҷ ⺻̴. ֳĸ  ÷
 MIT-pthreads flock() ϰ   ʱ ̴.

- Ʈ  ִٸ Ʈ ̷ ߿ .  Ʈ ϴ  
ѹ ϳ Ʈϴ ͺ  .
- FreeBSD ýۿ MIT-pthreads   FreeBSD 3.0   
Ʈ ϴ° . ̷ ϸ н  ϴ  ϸ(FreBSD 
  MIT-pthreads TCP/IP  ϴ ͺ ) ׸  Ű
 (intergrated?)Ǿ Ѵ.
- ̺̳ ÷ ܰ踦 üũϴ GRANT  ߸.


10.14 ο ˰ ٸ  ΰ?  VARCHAR/CHAR ؾ ϴ°?

mysql  SQL VARCHAR Ÿ . ״ mysql ڵ带 ϰ ̰ 
VARCHAR ķƮϴµ   ִ.

̺ VARCHAR, BLOB, TEXT ÷   row size Ѵ. ׿ܿ 
 row size Ѵ. CHAR  VARCHAR ÷ ø̼  
 ޵ȴ; Ѵ trailing space ÷ ö ŵȴ.

isamchk -d  ̿ ̺ ϴ  üũ  ִ.
(-d  "̺ " ǹ)

mysql  ٸ ̺   ִ; , ̳, .


  ̺
- ⺻ . ̺ VARCHAR, BLOB, TEXT ÷   .
-  CHAR, NUMERIC, DECIMAL ÷ ÷ ̿ space-padded ̴. (** space-
padded ̶ ؾ  ָŸȣؼ **)
- ſ 
- ĳϱ 
- ջ   . ֳĸ  ̿ ڵ尡 ġϱ ̴.
-   ڵ尡 ų  ýۿ   ø ġ ʴ´ٸ (isa
mchk ̿) ȭ ʿ.
-  ̳ ̺  ũ  ʿ Ѵ. 


̳ ̺
- ̺ VARCHAR, BLOB, TEXT ÷ ϰ   .
-  ڿ ÷ ̳ϴ.(4  ̸  ڿ )
- ÷ ڿ ÷ ų ('')  ÷ 0(NULL   ÷ 
  ƴϴ)  Ÿ Ʈ  ڵ տ ȴ. ڿ ÷ trailin
g space   zero ̸ ų  ÷ zero   Ʈ 
 ǥõǰ ũ  ʴ´.   ڴ ڳ뿡  Ʈŭ 
Ǿ ȴ.
-    ̺ ũ  .
-  ̸ Ȯϴ    Ʈϸ  ȭ ̴. ̷ 
      isamchk -r  ؾ Ѵ. (?) isamchk -ei 
tbl_name .
- ջ  ƴ. ֳĸ ڵ尡   ȭǰ ũ() 
 ̴.
- ̳  ̺ Ǵ   :
    3
    + (number of columns + 7) / 8
    + (number of char columns)
    + packed size of numeric columns
    + length of strings
    + (number of NULL columns + 7) / 8

 ũ 6 Ʈ  ִ. ̳ ڵ Ʈ ڵ尡 þ 
ũȴ.  ο ũ ּ 20Ʈ ̸, ׷  Ȯ Ƹ  
ũ  ̴. װ ƴ϶ ٸ ũ  ̴. isamchk -ed  󸶳  
ũ ִ üũ  ִ.  ũ isamchk -r    ִ.(** ?? **)
 
There is a penalty of 6 bytes for each link. A dynamic record is linked whenever an up
date causes an enlargement of the record. Each new link will be at least 20 bytes, so th
e next enlargement will probably go in the same link. If not, there will be another link. 
You may check how many links there are with isamchk -ed. All links may be removed 
with isamchk -r. 


 ̺

- б  ̺ pack_isam ƿƼ   ִ. Ȯ mysql ̸  
    뵵 pack_isam  Ǹ ־.
-  ڵ  mysql ǿ Ƿ pack_isam   pack_isam
  ̺   ִ. (̺  ÷ Ǿ ִ)
- ſ  ũ 뷮 . 
-  ڵ   ȴ.( ſ  ׼ overhead) ڵ  
̺  ū ڵ忡  (1-3 Ʈ) ȴ.  ÷ ٸ  ȴ.  
Ÿ  :

         Ϲ  ÷ ٸ Huffman ̴̺.
         Suffic  
         Prefix  
         0   ڴ 1Ʈ .
         integer ÷    ٸ, ÷ ִ  Ÿ 
ȴ.   BIGINT ÷   0 255 TINIINT ÷(1Ʈ) 
ȴ.
         ÷   θ Ǿ ִٸ, ÷ Ÿ ENUM ȯ
ȴ.
         ÷    Ͽ Ѵ.
-  ̳ ̳  ̺ ٷ  ִ. ׷ BLOB TEXT ÷ 
  .
- isamchk    ִ.

mysql ٸ ε Ÿ Ѵ. ׷ Ϲ Ÿ NISAM̴. ̰ B-tre
e ε̸  Ű  Ͽ (Ű +4)*0.67 ε  ũ⸦ 밭 
  ִ. (̰  Ű ĵ  Էµ   ̴)


String indexes are space compressed. If the first index part is a string, it will also be p
refix compressed. Space compression makes the index file smaller if the string column h
as a lot of trailing space or is a VARCHAR column that is not always used to the full 
length. Prefix compression helps if there are many strings with an identical prefix. 

ڿ ε  ȴ. ù° ε κ ڿ̶, prefix ȴ. 
ڿ ÷ ٷ trailing space ų   ̸  ʴ VA
RCHAR ÷  space  ε   ۰ . prefix   
  prefix   ϴ. 
{{<!-- This HTML file has been created by texi2html 1.52 (hacked by david@detron.se)
     from /dr1/my/masters/mysql/Docs/manual.texi on 2 Febuary 1999 -->
}}11. mysql ġũ Ʈ

⿡ mysql ġũ Ʈ(׸ crash-me)     Ѵ. ׷
  ۼ  ʾҴ. μ  'bench' 丮 ڵ  캸
ƾ Ѵ.(   캼  ִ. 
{{{{http://www.mysql.com/crash-me-choose.htmy)
}}
}}

̰ ڿ  ־ SQL   Ǵ ƴ ˷ִ ġũ̴. 

crash-me   Ǹ Ͽ ͺ̽ ϴ ɰ ɷ, ѻ  
ϴ α׷̴.     Ѵ:

         ϴ ÷ Ÿ
         ϴ ε 
         ϴ 
          ִ ũ
         VARCHAR ÷ ִ ũ
 : (taejun@hitel.net)

12. mysql ƿƼ

12.1 پ mysql α׷ 

mysql client ̺귯 Ͽ   ϴ  mysql Ŭ̾Ʈ  
ȯ  Ѵ:

Name                    Description
MYSQL_UNIX_PORT         ⺻ ; ȣƮ Ҷ 
MYSQL_TCP_PORT          ⺻ TCP/ip port
MYSQL_PWD               ⺻ н
MYSQL_DEBUG             Ҷ Debug-trace ɼ
TMPDIR                  ӽ ̺/ Ǵ 丮

MYSQL_PWD  ϴ  ȿ ϴ. 6.2 [Connecting] .

'mysql' Ŭ̾Ʈ   丮 ȯ  ϱ  MYSQL_HISTF
ILE ̶  Ѵ.

 MYSQL α׷ ſ پ ɼ ִ. ׷  MYSQL α׷ --
help ɼ Ѵ. --help ɼ ̿ α׷ پ ɼǿ    
  ִ.  , mysql --help  غ.

Ʒ  mysql α׷ ؼ ϰ ִ:

isamchk : mysql ̺  , , ȭ,  ƿƼ.   ֱ  
 忡 ڼ ϰ ִ. 13 .

make_binary_release : ϵ mysql ̳ʸ  . ٸ myql  
  ftp.tcx.e '/pub/mysql/Incoming'  ø.

msql2mysql : msql α׷ mysql ȯϴ  ũƮ.  츦 ٷ  
 ȯҶ  ̴.

mysql :  SQL . (GNU readline ȣȯ) ȣȭ  ȭ  
 ִ. ȭ ϴ ,   ƽŰ-̺  µȴ. ȭ
  ,   и  µȴ. (    
ɼ ̿ ٲ  ִ)   ũƮ   ִ:

shell> mysql database < script.sql > output.tab

Ŭ̾Ʈ ޸𸮰 ؼ   --quick ɼ . ׷  
   mysql_store_result()  mysql_use_result() Ѵ.

mysqlaccess : host, user, database     ũƮ.

mysqladmin : ͺ̽   ,  ̺ ε, ũ ̺ ÷, 
    ϴ ڿ ƿƼ. mysqladmin   , 
, (status)  Ȯ  ִ.

mysqlbug : mysql  Ʈ ũƮ. mysql ׸ ˸  ϴ ũƮ.

mysqld : SQL . ׻ ǰ ־ Ѵ.

mysqldump : mysql ͺ̽ SQL  ̳  е ؽ Ϸ 
ϴ ƿƼ. 

mysqlimport : LOAD DATA INFILE  ؽƮ  ڷḦ ̺ Էϴ 
ƿƼ. 12.2 .

mysqlshow : ͺ̽, ̺, ÷ ε   

mysql_install_db : ⺻  MYSQL  ̺ . ó ġ ȴ.

replace : msql2mysql Ǵ ƿƼ̴. ׷ پϰ   ִ. 
̳ ǥ Է ڿ ü  ִ.   ڿ Īϱ  ѵ Ȳ
 ýۿ (** ??) ڿ üϴµ   ִ.    
ɾ Ͽ a b üѴ:

shell> replace a b b a -- file1 file2 ...

safe_mysqld : mysqld  ϴ ũƮ.    ϰ α 
Ͽ   ϴ    å ִ.



12. 2 ؽƮ Ͽ  Է(?)ϱ

mysqlimport   ̽ LOAD DATA INFILE sql  Ѵ. κ
 ɼ LOAD DATA INFILE  ϴ. 7.15 [Load] .
  Ѵ:

shell> mysqlimport [options] filename ...

࿡  ؽƮ Ͽ Ͽ, mysqlimport ̸ Ȯڸ Ѵ. 
׸    ̺   ϴµ Ѵ.   vkdlfdlf
madl 'patient.txt', 'patient.text', 'patient'  patient ̺ ̸ Էµ 
.

mysqlimport   ɼ Ѵ:

-C, --compress : , Ŭ̾Ʈ  ϸ /Ŭ̾Ʈ ̿  
 Ѵ

-#, --debug[=option_string] : α׷  ()

-d, --delete : ؽƮ Ͽ Էϱ  ̺ 

--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--fields-terminated-by=...
        : LODA DATA INFILE  ɼǰ    ɼ

-f, --force :  .ؽƮ   ̺ , ٸ ִ   
ó(if a table for a text file doesn't exist, continue processing any remaining files)  
ɼ  , ̺  ´

--help :  

-h host_name, --host=host_name :  ȣƮ mysql  Ÿ Է. ⺻ 
localhost

-i, --ignore : --replace ɼ  .

-l, --lock-tables : ؽƮ Ϸ óϱ   ̺   Ǵ. ׷ 
  ̺ ȭ  ִ.

-L, --local : Ŭ̾Ʈ Է  . ⺻, localhost ϸ ؽƮ 
  ִٰ ȴ.

-pyour_pass, --password[=your_pass] :    ϴ йȣ. '=your_pass
'   ͹̳ο йȣ 

-P port_num, --port=port_num : ȣƮ   ϴ TCP/IP . (localhost 
ƴ ȣƮ   .   н  ϴ )

-r, --replace : --replace  --ignore ɼ unique key  ڵ尡 ߺǾ  
쿡 ȴ. --replcae   ,  unique key   ִ ڵ带 
üѴ. --ignore   , unique key   ڵ ȴ.  ɼ 
  ʴ´ٸ, ߺǴ Ű  ߰ߵǸ  ϰ ؽƮ  
 κ  ȴ.

-s, --silent : ħ .  ߻  .

-S /path/to/socket, --socket=/path/to/socket : ȣƮ(⺻ ȣƮ)   
ϴ  .

-u user_name, --user=user_name :    ϴ mysql  ̸. ⺻
 н α ̸.

-v, --verbose : Verbose . α׷ ࿡    .

-V, --version :   .



12.3 mysql  б  ̺ 
**  κ  . ϰ Ұ մϴ **

pack_isam  10 ̼ ̻ ϰų extended support      
 ߰ ƿƼ. ̳ʸθ ϹǷ Ư ÷  .  40%
-70% ̴. ޸𸮸 ϹǷ(mmap()) mmap() ۵   
. ϰ б  ̺ Ǹ BLOB Į  Ѵ.

   ִ mysql  б  ̺   д 
 ϴ. Ÿ ڼ  Ŵ .
13. ̺   ļ  isamchk ϱ


ͺ̽ ̺   , ̺ ,   ȭ Ҷ isamchk ƿƼ 
  ִ.    isamchk ϴ(ɼǿ    ), ̺ 
 ȹ   ,  isamchk پ  ϱ  isamchk 
ϴ  ϰ ִ.

{{}}13.1 isamchk ɾ 

isamchk    Ѵ:

shell> isamchk [options] tbl_name

ɼ isamchk    Ѵ. Ʒ Ѵ. (isamchk --help  ɼ
    ִ) ɼ  , isamchk  ̺ Ѵ.     
ϰų Ư ۾ ʿϸ Ʒ ϴµ ɼ Ѵ.

tbl_name ϱ ϴ ͺ̽ ̴̺. ͺ̽ 丮 ƴ ٸ 
 isamchk ϸ,  θ ؾ Ѵ. ֳϸ isamchk ͺ̽ ġ
 ؼ  ϱ ̴. , isamchk ۾Ϸ  ͺ̽ 丮 
ִ ƴ Ű  ʴ´; ͺ̽ ̺ شϴ  ٸ  
 װ  ۾   ִ.

Ѵٸ isamchk ࿡  ̺   ִ.  ̸ ε  
('.ISM'  )    ̷  '*.ISM'  Ͽ 丮  
   ִ.   ͺ̽ 丮 ִٸ   丮  
̺   ִ:

shell> isamchk *.ISM


If you are not in the database directory, you can check all the tables there by specifying the path to the dir
ectory:

ͺ̽ 丮  , 丮 θ Ͽ  ̺   
.

shell> isamchk /path/to/database_dir/*.ISM

 mysql data 丮 θ  ϵ ī带 Ͽ  ͺ̽  
̺   ִ:

shell> isamchk /path/to/datadir/*/*.ISM

isamchk  ɼ Ѵ:

-a, --analyze
 Analyze the distribution of keys. This will make some joins in MySQL faster.
 Ű  м. mysql Ư   .

-#, --debug=debug_options
 Output debug log. The debug_options string often is 'd:t:o,filename'.
  α . debug_options ڴ  'd:t:o,filename' ̴.

-d, --description
 ̺  

-e, --extend-check
 ̺ ſ ϰ .  Ư 쿡 ʿϴ. Ϲ isamchk  ɼ 
   ã  ִ.

-f, --force
 Overwrite old temporary files. If you use -f when checking tables (running isamchk without -r), isamchk 
will automatically restart with -r on any table for which an error occurs during checking.
   ӽ  . ̺ Ҷ -f ϸ(-r  isamchk ) isa
mchk ϴ   ߻ϴ ̺ ڵ -r ɼ Ѵ.


--help
  .

-i, --information
   ̺   .

-k #, --keys-used=#
 Used with -r. Tell the NISAM table handler to update only the first # indexes. Higher-numbered indexes 
are deactivated. This can be used to get faster inserts! Deactivated indexes can be reactivated by using isa
mchk -r.
-r  Բ . NISAM ̺ ڵ鷯 ù # ε Ʈ϶  ˷ش. Higher-nu
mberd(?) ε ȴ. ̰ insert  Ҷ Ѵ!  ε isamchk -r  
Ͽ Ȱȭȴ.

-l, --no-symlinks
Ҷ ɺ ũ  ʴ´. Ϲ isamchk ɺ ũ Ű ̺ 
Ѵ.



-q, --quick
 ϱ  -r  Բ . Ϲ  Ÿ  ǵ帮 ʴ´; ι° 
-q Ͽ  Ÿ  ϵ   ִ.

-r, --recover
 .  ʴ unique Ű ϰ    Ѵ.

-o, --safe-recover
 .    ; -r Ͽ ϴ ͺ . ׷ -r ٷ  
  쿡   ִ.
(** -r ٷ      𸣰ڳ׿... **)

-O var=option, --set-variable var=option
 .   Ʒ .

-s, --silent
ħ .  ߻   Ѵ. ΰ -s(-ss) ϸ  isamchk ſ 
 ۾   ִ.

-S, --sort-index
 Sort index blocks. This speeds up "read-next" in applications.
ε  . ø̼ǿ "read-next" ӵ .(??)

-R index_num, --sort-records=index_num
ε  ڵ带 .  ۾ ϸ Ÿ ߽ų  ְ  ε  SE
LECT  ORDER BY ۾ ӵ ų  ִ. (ó ϴ ð ſ !) 
̺ ε ȣ ã  SHOW INDEX Ѵ. SHOW INDEX isamchk 
ϴ Ͱ   ̺ ε ش. ε 1 Ͽ ȣ Ű
.

-u, --unpack
 Unpack a table that was packed with pack_isam.
pack_isam   ̺  .

-v, --verbose
Verbose .  . -d  -e  Բ   ִ.  -v (-vv, -vvv)Ͽ  
ڼϰ   ִ.

-V, --version
isamchk  .

-w, --wait
̺  ɷ  ٸ.

--set-variable (-O) ɼ     :

keybuffer             default value: 520192
readbuffer            default value: 262136
writebuffer           default value: 262136
sortbuffer            default value: 2097144
sort_key_blocks       default value: 16
decode_bits           default value: 9
{{}}

13.2 isamchk ޸ 

Memory allocation is important when you run isamchk. isamchk uses no more memory than you specify 
with the -O options. If you are going to use isamchk on very large files, you should first decide how much 
memory you want it to use. The default is to use only about 3M to fix things. By using larger values, you 
can get isamchk to operate faster. For example, if you have more than 32M RAM, you could use options s
uch as these (in addition to any other options you might specify):

޸ Ҵ isamchk   ߿ϴ.isamchk -O ɼǿ   ̻ ޸
  ʴ´. ſ ū Ͽ isamchk Ϸ ϸ, 󸶸  ޸𸮸  
  ؾ Ѵ. ⺻  ġµ 3M Ѵ.      
 isamchk   ִ.   32M ̻   ִٸ   ɼ  
 ִ. (ڰ  ɼǿ ߰Ͽ):

shell> isamchk -O sortbuffer=16M -O keybuffer=16M \
           -O readbuffer=1M -O writebuffer=1M ...

-O sortbuffer=16M  ϸ κ 쿡 ϴ.

Be aware that isamchk uses temporary files in TMPDIR. If TMPDIR points to a memory file system, you 
may easily get out of memory errors.
isamchk  TMPDIR ӽ  Ѵٴ Ϳ .  TMPDIR ޸  ý
 Ųٸ  ޸    ִ.


13. 3 ̺  
{{}}13.3 Setting up a table maintenance regime

 涧 ٸ ͺ  ̺ ϴ  .  ȹ 
 isamchk -s   ̺   ִ. -s ɼ ϸ isamchk ħ  ۵
 ϸ  ߻  ޽ Ѵ.

 Ҷ ̺ ϴ ͵  ̴.   Ʈ ߿ ý 
   Ϲ    ̺(̰ "expected crashed table"̶ 
) ؾ Ѵ.   '.pid' (μ ID)  Ŀ  ִٸ ֱ 24ð 
    ̺ ϱ  safe_mysqld isamchk ϴ ׽Ʈ ߰
 Ѵ.('.pid'  mysqld Ҷ  Ϲ mysqld ɶ ŵȴ. 
 Ҷ '.pid'  ִٴ  mysqld  Ǿٴ  Ÿ.)

  ׽Ʈ ֱٿ  ð '.pid' Ϻ ֱ ̺ ϴ ̴.

 Ϲ ý ߿  ̺   ִ. TcX 'crontab' Ͽ 
  Ͽ Ͽ ѹ 츮 ߿ ̺ ϵ cron ۾ 
:

35 0 * * 0 /path/to/isamchk -s /path/to/datadir/*/*.ISM

̷ ϸ ջ ̺   Ͽ ʿҶ ̺ ϰ   ִ.

⵿ 츮  ϰ ̺ ջ(ϵ  ƴ ٸ    
̺) 찡  츮 ϸε ϴ. (̰  ̴)

츮ŭ mysql  ŷڸ   ֱ 24ð Ʈ  ̺   
㸶 isamchk -s    õѴ.


13.4 ̺  

̺   踦   Ʒ  . ڿ ڼϰ   
 ̴.

isamchk -d tbl_name
̺     "describe() " isamchk . mysql  --skip-locki
ng ɼ   ϸ, isamchk  Ǵ  Ʈ ̺   
 Ѵ. ׷ isamchk describe 忡 ̺  ϱ  Ÿ 
  .

isamchk -d -v tbl_name
isamchk ϴ Ϳ   ڼ   -v ɼ ߰Ͽ verbose  
  ִ.

isamchk -eis tbl_name
̺  ߿  ش. ü ̺  о ϱ  ӵ .

isamchk -eiv tbl_name
-eiv     ǰ ִ  ش.


isamchk -d  :

ISAM file:     company.ISM
Data records:           1403698  Deleted blocks:         0
Recordlength:               226
Record format: Fixed length

table description:
Key Start Len Index   Type
1   2     8   unique  double
2   15    10  multip. text packed stripped
3   219   8   multip. double
4   63    10  multip. text packed stripped
5   167   2   multip. unsigned short
6   177   4   multip. unsigned long
7   155   4   multip. text
8   138   4   multip. unsigned long
9   177   4   multip. unsigned long
    193   1           text


isamchk -d -v  :

ISAM file:     company.ISM
Isam-version:  2
Creation time: 1996-08-28 11:44:22
Recover time:  1997-01-12 18:35:29
Data records:           1403698  Deleted blocks:              0
Datafile: Parts:        1403698  Deleted data:                0
Datafilepointer (bytes):      3  Keyfile pointer (bytes):     3
Max datafile length: 3791650815  Max keyfile length: 4294967294
Recordlength:               226
Record format: Fixed length

table description:
Key Start Len Index   Type                      Root Blocksize Rec/key
1   2     8   unique  double                15845376      1024       1
2   15    10  multip. text packed stripped  25062400      1024       2
3   219   8   multip. double                40907776      1024      73
4   63    10  multip. text packed stripped  48097280      1024       5
5   167   2   multip. unsigned short        55200768      1024    4840
6   177   4   multip. unsigned long         65145856      1024    1346
7   155   4   multip. text                  75090944      1024    4995
8   138   4   multip. unsigned long         85036032      1024      87
9   177   4   multip. unsigned long         96481280      1024     178
    193   1           text


Example of isamchk -eis  :

Checking ISAM file: company.ISM
Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:  98%  Packed:   17%

Records:          1403698    M.recordlength:     226   Packed:             0%
Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
Recordblocks:     1403698    Deleteblocks:         0
Recorddata:     317235748    Deleted data:         0
Lost space:             0    Linkdata:             0

User time 1626.51, System time 232.36
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 627, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 639, Involuntary context switches 28966


isamchk -eiv  :

Checking ISAM file: company.ISM
Data records: 1403698   Deleted blocks:       0
- check file-size
- check delete-chain
index  1:
index  2:
index  3:
index  4:
index  5:
index  6:
index  7:
index  8:
index  9:
No recordlinks
- check index reference
- check data record references index: 1
Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
- check data record references index: 2
Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
- check data record references index: 3
Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
- check data record references index: 4
Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
- check data record references index: 5
Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 6
Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 7
Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 8
Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 9
Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:   9%  Packed:   17%

- check records and index references
[LOTS OF ROW NUMBERS DELETED]

Records:          1403698    M.recordlength:     226   Packed:             0%
Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
Recordblocks:     1403698    Deleteblocks:         0
Recorddata:     317235748    Deleted data:         0
Lost space:             0    Linkdata:             0

User time 1639.63, System time 251.61
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0
Blocks in 4 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 10604, Involuntary context switches 122798



    ̺ Ÿ ε  ũ̴:

-rw-rw-r--   1 monty    tcx     317235748 Jan 12 17:30 company.ISD
-rw-rw-r--   1 davida   tcx      96482304 Jan 12 18:35 company.ISM


isamchk ϴ  ŸԿ   Ʒ . "keyfile" ε ̴. "Record"
 "row"  ̴.

ISAM file
        ISAM (index)  ̸.

Isam-version
        ISAM  .  ׻ 2.

Creation time
        Ÿ   ð.

Recover time
        ε/Ÿ  ֱٿ  ð.

Data records
        ̺ ִ ڵ .

Deleted blocks
          ϰ ִ . ̷  ̱  ̺ ȭ   
ִ. 13.5.3 [ȭ] .

Datafile: Parts
         ڵ  , 󸶳  Ÿ  ִ ˸. ȭ ڵ
  ȭ ̺ Data records  .

Deleted data
        ȸ   Ÿ Ʈ . ̷  ֱ  ̺ ȭ
  ִ. 13.5.3 [ȭ] .

Datafile pointer
        Ÿ   ũ Ʈ. Ϲ 2,3,4,5 Ʈ̴. κ 
 2Ʈ  Ѵ. ׷  mysql    .  ̺ 
̴ ڵ ּ(address)̴.  ̺ ̴ Ʈ ̴ּ.



Keyfile pointer
        ε   ũ Ʈ. Ϲ 1,2,3 Ʈ̴. κ ̺ 
2Ʈ  Ѵ. ׷  ũ mysql ڵ  ȴ. ׻  ּ
.

Max datafile length
        ̺  (.ISD ) ִ ũ. Ʈ.

Max keyfile length
        ̺ key file(.ISM )  ִ ũ. Ʈ.

Recordlength
         ڵ尡 ϰ ִ . Ʈ.

Record format
̺ ڵ带 ϴµ  .     ̸ ϰ ִ. 
ٸ  Compressed  Packed ̴.

table description
        ̺  Ű .  Ű  ڼ   :
Key
        Ű .
Start
        Where in the record this index part starts.
        ڵ忡 ε ϴ ġ.
Len
 How long this index part is. For packed numbers, this should always be the full length of the column. For 
strings, it may be shorter than the full length of the indexed column, because you can index a prefix of a st
ring column.
        ε κ . For packed numbers(  ڸ ???  ̻..), ÷ 
̰ Ǿ Ѵ. ڿ ε ÷  ̺ ۾ƾ Ѵ. ֳϸ ڿ 
÷ տ ε....

Index
         ε      ִ  Ÿ.
Type
        ε κ  Ÿ. packed, stripped, empty ɼ  NISAM Ÿ Ÿ
.
Root
        Ʈ ε  ּ.
Blocksize
         ε  ũ.⺻ 1024̴. ׷    Ҷ   
.
Rec/key
 This is a statistical value used by the optimizer. It tells how many records there are per value for this key. 
A unique key always has a value of 1. This may be updated after a table is loaded (or greatly changed) wit
h isamchk -a. If this is not updated at all, a default value of 30 is given.
        ȭ(optimizer) ϴ  . It tells how many records there are per valu
e for this key. unique Ű ׻ 1  .   isamchk -a ̺ ε Ŀ(Ǵ 
ſ  Ǿ) Ʈȴ.  Ʈ  ⺻ 30 ־.


 ù° , 9° Ű κκ  Ƽ-Ʈ Ű̴.


Keyblocks used
 What percentage of the keyblocks are used. Since the table used in the examples had just been reorganize
d with isamchk, the values are very high (very near the theoretical maximum).
        Ű ϰ ִ .(%)  ̺ isamchk ȭ(reorganize)Ǿ
   ſ .(̷ ִ밪 ſ )

Packed
MySQL tries to pack keys with a common suffix. This can only be used for CHAR/VARCHAR/DECIM
AL keys. For long strings like names, this can significantly reduce the space used. In the third example ab
ove, the 4th key is 10 characters long and a 60% reduction in space is achieved.
....  ̸  long ڿ    δ.  ׹°  4° Ű 10 
 long ̰ 60%  پ.
(**   ȵǴµ ̻簰  ٿ ڿ   δ  ׷ ̰ *
*)

Max levels
        How deep the B-tree for this key is. Large tables with long keys get high values.

Records
        ̺ ڵ.
M.recordlength
         ڵ .  渮 ڵ ̺   ڵ ̿ .

Packed
        MySQL strips spaces from the end of strings. The Packed value indicates the percentage saving
s achieved by doing this.


mysql ڳ  Ѵ.Packed value ̷ ؼ    Ѵ.

Recordspace used
        Ÿ  ϴ  .

Empty space
        Ÿ   ʴ  .

Blocks/Record
        ڵ  .(, ȭ ڵ尡  ũ Ǿ ִ) -
 ̺ ׻ 1̴.    1  ؾѴ.   ʹ Ŀ isa
mchk ̺ ȭ(reorganize)ؾ Ѵ. 13.5.3 [Optimizaiton] .

Recordblocks
        ϴ (ũ).  ˿ ̰ ڵ .

Deleteblocks
         (ũ).

Recorddata
        Ÿ  ϴ Ʈ.

Deleted data
         Ͽ ( ʴ) Ʈ.

Lost space
        ڵ尡 ſ ª ̷ ƮǸ ణ  Ұ ȴ.   ̷ 
 bytes հ̴.

Linkdata
         ̺   ,ڵ  ͷ ũȴ.Linkdata ̷ 
 ϴ   ̴.


pack_isam ̺ , isamchk -d   ̺ ÷  ߰  
Ѵ.̷     ǹϴ ؼ 12.3 [pack_isam] .


{{}}13.5 ļ  isamchk ϱ.
mysql Ÿ ϴµ ϴ   ϰ ׽ƮǾ. ׷ 
ͺ̽ ̺ ջ  ִ ܺ Ȳ ׻ ִ:



  ߿ mysqld μ ׾.
 ġ ϰ ǻͰ ˴ٿǾ( , ǻ   )
 ϵ 

̹  mysql ͺ̽ data ջ üũϰ ̿ óϴ  ؼ 
Ѵ.

ջ  ۾   ͺ̽  ̺ tbl_name ͺ̽ 丮  
 Ѵٴ Ϳ ؼ ϰ ִ  ߿ϴ: 

                    뵵
`tbl_name.frm'            ̺ () 
`tbl_name.ISD'            Ÿ 
`tbl_name.ISM'           ε 


  Ÿ پ  ջ Ѵ. ׷ κ  Ÿ ϰ 
 Ͽ .

isamchk '.ISD' (Ÿ)  纹  ۾ Ѵ.  '.ISD'  ϰ 
ο    ̸ ٲٸ鼭  ۾ ģ. --quick ɼ ϸ isamchk
 ӽ '.ISD'   ʴ´.  '.ISD'  Ȯϴٰ Ͽ '.ISD'  մ 
ʰ ο ε ϸ Ѵ. isamchk ڵ 'ISD'  ջǾ Ȯϰ 
Ǿ   ۾ ϹǷ --quick ɼ ϴ  ϴ. ΰ -quick 
   ִ. ̷  Ư (ߺ Ű ) Ҹ   '.ISD' 
 Ͽ  ذϷ Ѵ.Ϲ ΰ --quick ɼ ϴ  ۾
 ϱ  ũ    쿡 ϴ. ̷  isamchk ϱ
 ּ   ƾ Ѵ.

{{}}
13.5.1   ̺  

̺ ϱ    Ѵ:
:
isamchk tbl_name
          99.99% ߰  ִ.   ߰ ϴ  Ÿ ϰ 
õ ջ̴.(Ϲ   ʴ´) ̺ ϰ Ѵٸ Ϲδ ƹ
 ɼ  ʰų -s  --silent ɼ ־ isamchk ϴ ̴. 

isamchk -e tbl_name
         ɼ  ͸ ϰ Ѵ.( -e  "extended check"  ǹѴ)  
Ű Ȯ ڵ带 Ű ִ Ѵ.It does a check-read of every key for each row to ve
rify that they indeed point to the correct row.  Ű  ū ̺ ð  ɸ. is
amchk Ϲ ù°  ߰ϸ  .     Ѵٸ, --ver
bose (-v) ɼ ߰  ִ.  ɼ ߰ϸ isamchk  ִ 20    
  Ѵ. Ϲδ  isamchk (̺ ̸ ܿ ƹ μ ) 
ϴ.

isamchk -e -i tbl_name
         ɰ . ׷ -i ɼ ̸ isamchk  踦 Ѵ.


13.5.2 ̺ 

ջ ̺ ¡Ĵ Ϲ ǰ ڱ ǰ    :
`tbl_name.frm' is locked against change
Can't find file `tbl_name.ISM' (Errcode: ###)
Got error ### from table handler (Error 135 is an exception in this case)
Unexpected end of file
Record file is crashed

̷ , ̺ ľ Ѵ. isamchk Ϲ ߸  ϰ κ ģ.

  Ʒ ϴ´ 4ܰ谡 ִ.ϱ   Ÿ̽ 丮 
̵ϰ(cd  ̿) ̺  ۹̼ Ȯؾ Ѵ. mysqld   մ н 
ڰ   ִ Ȯؾ Ѵ. ( ۾ Ϸ . ֳϸ Ϸ 
 ؾ ϱ ̴)  ؾ Ѵٸ Ͽ   ־ .

1ܰ : ̺ 

isamchk *.ISM Ǵ ( ð ִٸ isamchk -e *.ISM). ʿ    -s (s
ilent) ɼ Ѵ.

isamchk  ִٰ ˸ ̺ ľ Ѵ. ̷ ̺  2ܰ Ѿ
.

ϸ鼭   (out of memory  ) Ǵ isamchk  ߾  3ܰ 
Ѿ.


2ܰ :   

 isamchk -r -r tbl_name õѴ. (-r -q "  " ǹ) ̰ Ÿ  
 ʰ ε   õѴ. Ÿ   Ǿ ְ  ũ Ÿ 
ϳ Ȯ ġ Ű ִٸ, Ȱϰ ۵ ϰ ̺ ĥ ̴.(If the data fi
le contains everything that it should and the delete links point at the correct locations within the data file, t
his should work and the table is fixed.)  ̺ ġ. װ ƴ϶   Ѵ:

        1. ϱ  Ÿ   
        2. isamchk -r tbl_name .(-r  " " ǹ) ׷ Ÿ Ͽ Ȯ 
 ڵ  ڵ带 ϰ ε  籸Ѵ.
        3.   ϸ, isamchk --safe_recover tbl_name . Safe recovery  
   ϸ Ϲ       쿡   ִ.(׷
  )


ϸ鼭   (out of memory  ) Ǵ isamchk  ߾  3ܰ 
Ѿ.


3ܰ :  

ε  ù 16k  ıǰų Ȯ     , Ǵ ε 
  쿡 ̹ ܰ ´. ̰ ο ε   Ѵ.   
:

        1. Ÿ   ҷ ̵.
        2. ο() Ÿ ε    table description  :
                shell> mysql db_name
                mysql> DELETE FROM tbl_name;
                mysql> quit
        3.  Ÿ  Ӱ   Ϸ . ( Ÿ  ο 
Ϸ ű ; ߸Ǿ  纻 ϱ  ̴)

2ܰ . isamchk -r -q   ۵  ̴. (  Ǹ ȵȴ. This shoul
dn't be an endless loop).



4ܰ : ſ  

description   ջ Ծ 쿡 ̹ ܰ ´. description  ̺ 
 Ŀ   ʱ , ̷   ܼ ȵȴ.

        1.  description   3ܰ Ѿ.  ε   
 ְ 2ܰ Ѿ.   isamchk -r  ؾ Ѵ.
        2.   Ȯ  ̺  ˰ ִٸ, ٸ ͺ
 ̺ 纻 . ο Ÿ  ϰ ٸ ͺ̽ description 
 ε  ջ ͺ̽ ű. ̷ ϸ ο description  ε 
    Ÿ ϸ  ִ. 2ܰ  ε  籸Ѵ.

13.5.3 ̺ ȭ

ڵ带 ϰų ׷̵ ϸ鼭  ȭ ڵ带  ʿϰ  
 ϱ    isamchk Ѵ:

        shell> isamchk -r tbl_name

SQL OPTIMIZE TABLE  ̿Ͽ   ̺ ȭ  ִ. OPTIMIZE TA
BLE   isamchk  .

 isamchk ̺  ų  ִ  ɼ   ִ:

-S, --sort-index
        high-low  ε Ʈ  . ˻ ȭϰ Ű  ̺ ˻ 
 Ѵ.
-R index_num, --sort-records=index_num
        ε  ڵ带 . Ÿ ȭϰ  ε ϴ SELECT  O
RDER BY ۷̼ ӵ Ų. (ó  ϴ ð û !) ̺
 ε ȣ ȮϷ SHOW INDEX ϸ Ǹ, SHOW INDEX isamchk ε
 ˻ϴ  ̺ ε ش. ε 1 ȣ Ű.
-a, --analyze
        ̺ Ű  м. ߿ ̺ ڵ带     
Ų.
15. mysql ODBC 


mysql MyODBC α׷  ODBC   Ѵ.

15.1 MyODBC  ϴ ü

MyODBC  95 NT 32Ʈ ODBC(2.50)  0 ̴̹. 츮  
 3.x   α׷ ֱ ٶ.
(** ,  ɷ¾ȵ **)


15.2 MyODBC  ִ 

ODBC ׼, Admndemo.exe, C++-, Centura Team Developer (formerly Gupta SQL/Win
dows), ݵǻ(ֶ󸮽), ũŻ Ʈ, , , iHTML, FileMaker Pro, 
,  Notes 4.5/4.6, SBSS, perl DBD-ODBC, Ķ󵶽, Ŀ, VC++, ־ 
 ׽õǾ. 

MyODBC   ۵ϴ ٸ  ø̼  myodbc@tcx.se    ּ
~ (**   ־ μ **)

 εġ, ODBC Ŵ α ϰ MyODBC α׿ ؼ ˰ ʹ. ̷ 
   ̳ ̴µ   ̴.

MyODBC log   MyODBC / ȭ 'Trace MyODBC' ɼǿ üũ Ѵ. 
α״ `c:\myodbc.log'  ϵ ̴.  ɼ  ۵Ϸ MYSQL2.DLL  
϶ MYSQL.DLL  ؾ Ѵٴ  !


15.3 MyODBC   ۵ϴ α׷

κ α׷ myodbc   ۵Ѵ. ׷ Ʒ  Ͽ ִ  츮 
 ׽Ͽų ٸ   ۵Ѵٰ Ȯ  ̴::

׼
׼ʹ  ۵Ѵ:
        - ̺ ̸Ӹ Ű ؾ Ѵ.
        - Ʈ Ǳ ϴ  ̺ timestamp  ־ Ѵ.
        - double float ʵ常 ؾ Ѵ. single floats  񱳸 ϸ Ѵ.
        - mysql   'Return matching rows' ɼ ʵ忡 üũ .
        - NT ׼ BLOB ÷ OLE OBJECTS  Ѵ.  MEMO ÷ 
 ϸ ALTER TABLE  ̿ ÷ TEXT  ٲ.

 
 ۵Ѵ   ִ:

dates    CONCAT() Լ Ͽ string μ select  . 
 :

        select CONCAT(rise_time), CONCAT(set_time) from sunrise_sunset;


ڿμ   97 time   νĵ ̴.

  CONCAT()  ODBC ÷ "string type" ϵ ̴ 
. CONCAT()  , ODBC ÷ time Ÿ νϸ  ̰ ϴµ 
Ѵ.

̰  ׶  .  ڵ ڿ(string) time 
ȯѴ. ҽ ؽƮ ̶   ̴. ׷ ҽ  ÷ Ȯ Ÿ
 ϴ ODBC ̶ и  ̴.

 odbcadmin
ODBC ׽Ʈ α׷.


 
DBE 3.2    ؾ Ѵ. mysql   'Don't optimize column width' 
ɼ ʵ忡 üũ Ѵ.

, ⿡ myodbc   ODBC ϰ BDE  ϱ    
 ڵ尡 ִ. (BDE        ִ BDE Alias 
͸ ʿ Ѵ) : (Thanks to Bryan Brunton bryan@flesherfab.com for this)

fReg:= TRegistry.Create;
          fReg.OpenKey('\Software\ODBC\ODBC.INI\DocumentsFab', True);
          fReg.WriteString('Database', 'Documents');
          fReg.WriteString('Description', ' ');
          fReg.WriteString('Driver', 'C:\WINNT\System32\myodbc.dll');
          fReg.WriteString('Flag', '1');
          fReg.WriteString('Password', ");
          fReg.WriteString('Port', ' ');
          fReg.WriteString('Server', 'xmark');
          fReg.WriteString('User', 'winuser');
          fReg.OpenKey('\Software\ODBC\ODBC.INI\ODBC Data Sources', True);
          fReg.WriteString('DocumentsFab', 'MySQL');
          fReg.CloseKey;
          fReg.Free;

          Memo1.Lines.Add('DATABASE NAME=');
          Memo1.Lines.Add('USER NAME=');
          Memo1.Lines.Add('ODBC DSN=DocumentsFab');
          Memo1.Lines.Add('OPEN MODE=READ/WRITE');
          Memo1.Lines.Add('BATCH COUNT=200');
          Memo1.Lines.Add('LANGDRIVER=');
          Memo1.Lines.Add('MAX ROWS=-1');
          Memo1.Lines.Add('SCHEMA CACHE DIR=');
          Memo1.Lines.Add('SCHEMA CACHE SIZE=8');
          Memo1.Lines.Add('SCHEMA CACHE TIME=-1');
          Memo1.Lines.Add('SQLPASSTHRU MODE=SHARED AUTOCOMMIT');
          Memo1.Lines.Add('SQLQRYMODE=');
          Memo1.Lines.Add('ENABLE SCHEMA CACHE=FALSE');
          Memo1.Lines.Add('ENABLE BCD=FALSE');
          Memo1.Lines.Add('ROWSET SIZE=20');
          Memo1.Lines.Add('BLOBS TO CACHE=64');
          Memo1.Lines.Add('BLOB SIZE=32');

          AliasEditor.Add('DocumentsFab','MySQL',Memo1.Lines);


 C++
BDE 3.0 ׽ ߴ. ϰ ˷  ̺ Ű    ʵ
 Ʈ ʴ´. ׷ BDE  ƴ   index PRIMARY  
̸Ӹ Ű ν ϴ  δ.
 Tested with BDE 3.0. The only known problem is that when the table schema changes, q
uery fields are not updated. BDE however does not seem to recognize primary keys, onl
y the index PRIMARY, though this has not been a problem.


 ־ 
̺ Ʈ ϰ Ϸ ̺ ̸Ӹ Ű ؾ Ѵ.
(**  κ   ps Ͽ µ Ʈ ֳ׿... **)
{{}}


15.4 ODBC  α׷  

 95  ̸ ϴ Ϳ   ִ:

-  IP ּ .
-'lmhosts' Ͽ   ߰:
        ip hostname
 
        194.216.84.21 my
- DNS Ͽ PC 

"ODBC setup"  ä :

Windows DSN name:   taejun
Description:        This is my love database
MySql Database:     love
Server:             194.216.84.21
User:               taejun
Password:           my_password
Port:

 DSN ̸ ʵ   ODBC ¾  ̴.

ODBC ¾ ȭ鿡 , , н,  ʵ忡   ʿ . ׷ 
 صθ    õ  ⺻ ȴ. ׶  ٲ  
.

Ʈ ȣ ־ , ⺻ Ʈ(3306) ȴ.


15.5 ODBC AUTO_INCREMENT ÷  

Ϲ  INSERT   ڵ Ǵ ID    ̴ִ. OD
BC     ִ. (auto  AUTO_INCREMENT ʵ ):

INSERT INTO foo (auto,text) VALUES(NULL,'text');
SELECT LAST_INSERT_ID();

Ǵ, ٸ ̺ ID ԷѴٸ     ִ:

INSERT INTO foo (auto,text) VALUES(NULL,'text');
INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text');

ٸ ODBC ø̼(ּ ̿ ׼) ϰ ϱ   
 Ӱ Էµ  ãµ   ִ:

SELECT * FROM tbl_name WHERE auto IS NULL;
17. Ϲ  ذ 

17. 1. ͺ̽ (?)

ͺ̽ ϴ  Ϲ  Ʈ α׸ ̿ϴ ̴. 9.2 [Th
e update log] .   ͷ ۵ϴ ͺ̽ ϳ(Ͱ  ) 
̺ ۵ϴ ٸ ϳ ͺ̽ ʿϴ. ̺긦 ƮϷ 
 mysql < update_log ϸ ȴ. ̵ ͺ̽ ´ ȣƮ, , н
 ɼ Ѵ. ׸ Է°  ͺ̽ Ʈ α׸ Ѵ.

̺   ٸ,  縦   (  ð 
) ̺ Էµǰų   ãƳ  TIMESTAMP ÷   ְ 
̷Ǵ ͺ̽  ڷḸ 縦 Ѵ.

Ʈ α(for deletes) timestamps(on both sides)  Ͽ ΰ 
 Ʈϴ ý   ִ. ׷ ̷  ΰ ends(?)  
 Ϳ 浹   ־ Ѵ. Ƹ   ƮǾ 
ϱ    ϰ  ̴.

It is possible to make a two-way updating system using both the update log (for delet
es) and timestamps (on both sides). But in that case you must be able to handle confl
icts when the same data have been changed in both ends. You probably want to keep the 
old version to help with deciding what has been updated.

  () SQL ̷ , ͺ̽ Ʈϴ 忡
  Լ ؼ ȵȴ; ⿡  ͺ̽   ȯ
   ִ:

     DATABASE()
     GET_LOCK() and RELEASE_LOCK()
     RAND()
     USER(), SYSTEM_USER() or SESSION_USER()
     VERSION()

timestamp ʿ 쿡 ̷Ǵ     time Լ ϰ 
  ִ. LAST_INSERT_ID()  ϰ   ִ.

All time functions are safe to use, as the timestamp is sent to the mirror if needed. 
LAST_INSERT_ID() is also safe to use.


17.2 ͺ̽ 

mysql ̺ Ϸ Ǳ  ϱⰡ .  ϰ  ۾  
õ ̺ LOCK TABLES . 7.23 [LOCK TABLES/UNLOCK TABLES synta
x] .  б  ʿϴ; ͺ̽ 丮  纻 
 ȿ ٸ 忡 ̺ Ǹ    ִ. SQL   
ϰ Ѵٸ SELECT INTO OUTFILE   ִ.

ͺ̽ ϴ ٸ  mysqldump α׷ ϴ ̴:

ͺ̽  Ǯ  :
     
shell> mysqldump --tab=/path/to/some/dir --lock-tables --opt
     
      Ʈ  ʴ ϰ  ̺ (`*.frm', `*.ISD' , `*.
     ISM' )   ִ. mysqld ǰ  ߾ Ѵ. ׷ 
     --log-update ɼ ٽ . ''hostname.n'   α  
      ̴. n mysqladmin refresh, mysqladmin flush-logs, the FLUSH LOGS , 
     Ǵ  Ҷ ϴ ̴. ̷  α  ̿ mys
     qldump ϰ  ͺ̽ ȭ  ()ϴµ ʿ 
        ִ. 
     
ϰ Ѵٸ,  isamchk -r  ̺ .   99.9% 
 ȴ. isamchk ϸ   :

        
      mysqldump  Ѵ.
     Ʈ α׿ Ʈ ٽ ϱ    Ѵ:
     
shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql

ls Ȯ  α  µ ȴ.

 SELECT * INTO OUTFILE 'file_name' FROM tbl_name ̿     
 ڵ尡 ߺǴ  ϱ   LOAD DATA INFILE 'file_name' REPLACE ...  
 ̿   ִ. ̰쿡 ̺ PRIMARY Ű UNIQUE Ű ʿϴ. RE
PLACE Ű ο ڵ忡 unique Ű    ڵ尡 ߺǴ  
 ڵ带 ο ڵ üѴ.


17.3  ӽſ  mysqld  ϱ
 ӽſ   ϱ   ִ.  ,   ״
 ΰ ο mysql  ׽ϴ 찡   ִ. Ǵ ٸ 
  mysql ġ ϱ ϴ ͳ    ִ.

  ϱ ϸ,    ٸ TCP/IP Ʈ  Ϸ  
ؼ   TCP/IP Ʈ   û ʵ   ִ.

̹ ϰ ִ  ⺻ Ʈ  Ϸ Ǿ ִٰ غ. ׷ 
   ο  :

shell> ./configure  --with-tcp-port=port_number \
             --with-unix-socket=file_name \
             --prefix=/usr/local/mysql-3.22.9

⼭ port_number file_name ⺻ Ʈ     οʹ ޶ϸ, -
-prefix   ġǾ ִ mysql ٸ 丮 ؾ Ѵ.

Here port_number and file_name should be different than the default port number and s
ocket file pathname, and the --prefix value should specify an installation directory 
different than the one under which the existing MySQL installation is located.

   ǰ ִ mysql  ϰ  Ȯ  ִ:

shell>; mysqladmin -h hostname --port port_number variables

ϰ ִ Ʈ ǰ ִ mysql  ִٸ,  ̸  mysql 
 ߿      ִ.  mysqld  ϰ Ű 
 ý ʱȭ ũƮ(Ϲ ''mysql.server') ؾ Ѵ.

ٸ Ʈ   ϱ  ο mysql   ʿ . 
safe_mysqld   ɼ Ʈ    ִ:

shell>; /path/to/safe_mysqld --socket=file-name --port=file-name

 ͺ̽ 丮 α׸ ϵ ϸ鼭  ٸ  ϰ 
Ѵٸ, safe_mysqld  --log  --log-update  ̿ α  ̸  ־
 Ѵ. ׷     α Ͽ  Ϸ  ̴.

 : Ϲ  ͺ̽ ڷḦ Ʈϴ ΰ  ؼ 
ȵȴ! ü fault-free ý ŷ(locking)  ʴ´ٸ,    
   ̴. (** fault-free Ȯ  𸣰ڱ. Ƹ  Ŵ
  ִٸ   ϴ  Դϴ. **)

ι  ٸ ͺ̽ 丮 ϱ ϸ safe_mysqld  --datadir
=path ɼ   ִ.

ٸ Ʈ  mysql  ϱ Ѵٸ     ִ:

         Ŭ̾Ʈ  ɼ  . --host 'hostname'  --port=port-n
umer or [--host localhost] --socket=file-name.
         C  α׷ mysql    Ʈ  ڸ ش.
         Ŭ̾Ʈ ϱ  MYSQL_UNIX_PORT  MYSQL_TCP_PORT ȯ  
. Ϲ Ư ̳ Ʈ ϸ,  'login' Ͽ ȯ  
. 12.1 [Programs] .
         Ȩ 丮 '.my.cnf' Ͽ ⺻ ϰ TCP/IP  .  4.15.4 
[Option files] .





18 MySQL client tools and APIs

18.1 MySQL C API

C API ڵ MYSQL Բ Ǹ, ̰ mysqlclient ̺귯 ԵǾ C 
α׷ Ͽ ͺ̽   ֵ ش. MySQL ҽ   
Ŭ̾Ʈ C ڵǾ , C API ϴ    ã   
 Ŭ̾Ʈ ڵ带   ִ.

18.2 C API datatypes
-----------------------------------------------------------------------------------------
| 	      | 								|
-----------------------------------------------------------------------------------------
|  MYSQL	      |  ̽ ϳ  ٷ üμ       |
|		      | MySQL Լ鿡 ȴ.					|
-----------------------------------------------------------------------------------------
|  MYSQL_RES	      |  ϴ ǵ(SELECT, SHOW, DESCRIBE, EXPLAIN) 	|
|		      |  ǥϴ üμ   μ ϵǴ    |
|		      |   (result set)̶ θ.				|
-----------------------------------------------------------------------------------------
|  MYSQL_ROW	      |   ͸ ǥϴ  ̴.  ̰ counted  |
|		      | byte string 迭 Ǿ ִ. ʵ尪  ͸  |
|		      |   ִ 쿡  ο null Ʈ       |
|		      | ֱ     null-terminated string ٷ  |
|		      | .  mysql_fetch_row() Լ ȣν    |
|		      | .								|
-----------------------------------------------------------------------------------------
|  MYSQL_FIELD	      | ʵ , , ũ  ʵ忡   ϴ ü	|
|		      | ̴.  ü  Ʒ  ȴ.    |
|		      | 忡 Ͽ mysql_fetch_field() Լ ȣν  ʵ忡 |
|		      |  MYSQL_FIELD ü    ִ. ʵ忡 ִ      |  
|		      |    ü  ƴϸ,  ʹ 		|
|		      | MYSQL_ROW ü  ȴ.				|
-----------------------------------------------------------------------------------------
|  MYSQL_FIELD_OFFSET | MySQL ʵ Ʈ  (offset) ǥϴ type-safe  |
|		      | ̴(mysql_field_seek() Լ ȴ). Offset  |
|		      |  ʵ ̸ 0 Ѵ. 				|
-----------------------------------------------------------------------------------------
|  my_ulonglong	      |  , mysql_affected_rows(), mysql_num_rows(), 		|
|		      | mysql_insert_id() Լ Ǵ ̴.   0   |
|		      | 1.84e19    ִ.  ýۿ 		|
|		      | my_ulonglong   ϴµ    ִµ,    |
|		      | 쿡   ϱ ؼ ̰ unsigned long   |
|	      	      | ȯ  '%lu' Ʈ  Ѵ.    :  |
|		      |									|	
|		      | printf (Number of rows: %lu\n", (unsigned long) \		|
|		      | mysql_num_rows(result));					|
-----------------------------------------------------------------------------------------

MYSQL_FIELD ü    :

char * name 
	- ʵ ̸

char * table
	-  ʵ尡  ʵ(calculated field) ƴ ,  ʵ带  ִ 
	  ̺ ̸.  ʵ  NULL ͸ 

char * def
	-  ʵ Ʈ (mysql_list_fields() Լ ϴ 쿡 ȴ)

enum enum_field_types type
	- ʵ (type). ʵ   ̺  ߿ ϳ̴.

-------------------------------------------------------------------------
| Type value		| Type meaning					|
-------------------------------------------------------------------------
| FIELD_TYPE_TINY	| TINYINT field					|
-------------------------------------------------------------------------
| FIELD_TYPE_SHORT	| SMALLINT field				|
-------------------------------------------------------------------------
| FIELD_TYPE_LONG	| INTEGER field					|
-------------------------------------------------------------------------
| FIELD_TYPE_INT24	| MEDIUMINT field				|
-------------------------------------------------------------------------
| FIELD_TYPE_LONGLONG	| BIGINT field					|
-------------------------------------------------------------------------
| FIELD_TYPE_DECIMAL	| DECIMAL or NUMERIC field			|
-------------------------------------------------------------------------
| FIELD_TYPE_FLOAT	| FLOAT field					|
-------------------------------------------------------------------------
| FIELD_TYPE_DOUBLE	| DOUBLE or REAL field				|
-------------------------------------------------------------------------
| FIELD_TYPE_TIMESTAMP	| TIMESTAMP field				|
-------------------------------------------------------------------------
| FIELD_TYPE_DATE	| DATE field					|
-------------------------------------------------------------------------
| FIELD_TYPE_TIME	| TIME field					|
-------------------------------------------------------------------------
| FIELD_TYPE_DATETIME	| DATETIME field				|
-------------------------------------------------------------------------
| FIELD_TYPE_YEAR	| YEAR field					|
-------------------------------------------------------------------------
| FIELD_TYPE_STRING	| String (CHAR or VARCHAR) field		|
-------------------------------------------------------------------------
| FIELD_TYPE_BLOB	| BLOB or TEXT field (use max_length to 	|
|			| determine the maximum length)			|
-------------------------------------------------------------------------
| FIELD_TYPE_SET	| SET field					|
-------------------------------------------------------------------------
| FIELD_TYPE_ENUM	| ENUM field					|
-------------------------------------------------------------------------
| FIELD_TYPE_NULL	| NULL-type field				|
-------------------------------------------------------------------------
| FIELD_TYPE_CHAR	| Deprecated: use FIELD_TYPE_TINY instead   	|
-------------------------------------------------------------------------

IS_NUM() ũ(macro) ̿Ͽ ʵ尡 (numeric type)ΰ ׽Ʈ  ִ. 
IS_NUM() İ(type value) μ Ѱ  ʵ尡 ̸ 'TRUE' Ѵ:

if (IS_NUM(field->type)) {
    printf("Field is numeric\n");
}

unsigned int length 
	- ʵ (width)

unsigned int max_length 
	-  ¿  ʵ ִ (maximum width). mysql_list_fields() Լ 
	  ϴ    ʵ ִ ̰ ȴ.

unsigned int flags 
	- ʵ忡  Different bit-flags. ÷  0 Ȥ  Ʈ (bit set) ߿ 
	  ϳ̴.

-----------------------------------------------------------------
|  ÷   		|	ǹ				|
-----------------------------------------------------------------
|  NOT_NULL_FLAG	| Field can't be NULL			|
-----------------------------------------------------------------
|  PRI_KEY_FLAG		| Field is part of a primary key	|
-----------------------------------------------------------------
|  UNIQUE_KEY_FLAG	| Field is part of a unique key		|
-----------------------------------------------------------------
|  MULTIPLE_KEY_FLAG	| Field is part of a key		|
-----------------------------------------------------------------
|  UNSIGNED_FLAG	| Field has the UNSIGNED attribute	|
-----------------------------------------------------------------
|  ZEROFILL_FLAG	| Field has the ZEROFILL attribute	|
-----------------------------------------------------------------
|  BINARY_FLAG		| Field has the BINARY attribute	|
-----------------------------------------------------------------
|  AUTO_INCREMENT_FLAG	| Field has the AUTO_INCREMENT attribute|
-----------------------------------------------------------------
|  ENUM_FLAG		| Field is an ENUM (deprecated)		|
-----------------------------------------------------------------
|  BLOB_FLAG		| Field is a BLOB or TEXT (deprecated)	|
-----------------------------------------------------------------
|  TIMESTAMP_FLAG	| Field is a TIMESTAMP (deprecated)	|
-----------------------------------------------------------------

BLOB_FLAG, ENUM_FLAG, TIMESTAMP_FLAG ÷׵ ̵ ÷׵ ڽ 
(type) Ӽ ǥϱ ٴ ʵ  ǥϱ   ÷׵ ϴ 
  ʴ.   'field->type'  FIELD_TYPE_BLOB, FIELD_TYPE_ENUM, Ȥ 
FIELD_TYPE_TIMESTAMP ϴ  . Ʒ  ÷   
 Ͽ Ѵ:

if (field->flags & NOT_NULL_FLAG) {
    printf("Field can't be null\n");
}

÷  Ҹ (boolean status) ϴµ   ũ(macro) 
  ִ:

	IS_NOT_NULL(flags)	 ʵ尡 'NOT NULL' ǵǾ  
	IS_PRI_KEY(flags)	 ʵ尡 primary key 쿡 
	IS_BLOB(flags)		 ʵ尡 BLOB̳ TEXT 쿡 

unsigned int decimals 
	-  ʵ(numeric field)  ڸ(number of decimal)


18.3 C API function overview
	
-------------------------------------------------------------------------------------------------
| Function			|	Description						|
-------------------------------------------------------------------------------------------------
| mysql_affected_rows()		|  ֱ UPDATE, DELETE, INSERT ǿ   		|
|				|    Ѵ.					|
-------------------------------------------------------------------------------------------------
| mysql_close()			|   Ѵ.					|
-------------------------------------------------------------------------------------------------
| mysql_connect()		|  ϸ  Լ ϱ⺸ٴ 			|
-------------------------------------------------------------------------------------------------
|				|  mysql_real_connect() Լ ϴ  ȴ.		|
| mysql_create_db()		| DB Ѵ.  Լ ϱ⺸ٴ SQL ɾ 		|
|				| CREATE DATABASE  ϴ  .			|
-------------------------------------------------------------------------------------------------
| mysql_data_seek()		|   ¿   ã´.				|
-------------------------------------------------------------------------------------------------
| mysql_debug()			| ־ ڿ DBUG_PUSH Ѵ.				|
-------------------------------------------------------------------------------------------------
| mysql_drop_db()		| DB Ѵ.  Լ ϱ⺸ٴ SQL ɾ DROP 	|
|				| DATABASE  ϴ  .				|
-------------------------------------------------------------------------------------------------
| mysql_dump_debug_info()	|  Ͽ   α׿ ⵵ Ѵ.		|
-------------------------------------------------------------------------------------------------
| mysql_eof()			|    ° ǥѴ.  Լ		|
|				| ϱ⺸ٴ mysql_errno() mysql_error()Լ ȴ.	|
-------------------------------------------------------------------------------------------------
| mysql_errno()			|  ֱٿ  MySQL Լ  ѹ Ѵ.	|
-------------------------------------------------------------------------------------------------
| mysql_error()			|  ֱٿ  MySQL Լ  ޼ Ѵ.	|
-------------------------------------------------------------------------------------------------
| mysql_escape_string()		| SQL   Ư ڸ óѴ. 				|
-------------------------------------------------------------------------------------------------
| mysql_fetch_field()		| ̺  ʵ (type) Ѵ.			|
-------------------------------------------------------------------------------------------------
| mysql_fetch_field_direct()	| ־ ʵ ȣ  ʵ  Ѵ.			|
-------------------------------------------------------------------------------------------------
| mysql_fetch_fields()		|  ʵ   迭 Ѵ.			|
-------------------------------------------------------------------------------------------------
| mysql_fetch_lengths()		|    ÷ ̸ Ѵ.			|
-------------------------------------------------------------------------------------------------
| mysql_fetch_row()		| κ   ´.				|
-------------------------------------------------------------------------------------------------
| mysql_field_seek()		| ÷Ŀ Ư ÷ ´.				|
-------------------------------------------------------------------------------------------------
| mysql_free_result()		| ¿   ޸𸮸 Ѵ.			|
-------------------------------------------------------------------------------------------------
| mysql_get_client_info()	| Ŭ̾Ʈ   Ѵ.				|
-------------------------------------------------------------------------------------------------
| mysql_get_host_info()		|  ῡ    ڿ Ѵ.		|
-------------------------------------------------------------------------------------------------
| mysql_get_proto_info()	| ῡ    Ѵ.			|
-------------------------------------------------------------------------------------------------
| mysql_get_server_info()	|   ѹ Ѵ.					|
-------------------------------------------------------------------------------------------------
| mysql_info()			|  ֱٿ  ǿ   Ѵ.		|
-------------------------------------------------------------------------------------------------
| mysql_init()			| MYSQL ü ϰų ʱȭѴ.				|
-------------------------------------------------------------------------------------------------
| mysql_insert_id()		| AUTO_INCREMENT ʵ忡 Ͽ  ֱٿ  ID .	|
-------------------------------------------------------------------------------------------------
| mysql_list_dbs()		|  ԽĿ  ĪǴ DB ̸ Ѵ.		|
-------------------------------------------------------------------------------------------------
| mysql_list_fields()		|  ԽĿ  ĪǴ ʵ ̸ Ѵ.		|
-------------------------------------------------------------------------------------------------
| mysql_list_processes()	|    Ʈ Ѵ.			|
-------------------------------------------------------------------------------------------------
| mysql_list_tables()		|  ԽĿ  ĪǴ ̺ ̸ Ѵ.	|
-------------------------------------------------------------------------------------------------
| mysql_num_fields()		|   ÷  Ѵ.				|
-------------------------------------------------------------------------------------------------
| mysql_num_rows()		|     Ѵ.				|
-------------------------------------------------------------------------------------------------
| mysql_ping()			|    ǰ ֳ üũϸ, ʿϸ 	|
|				|  翬Ѵ.						|
-------------------------------------------------------------------------------------------------
| mysql_query()			| Null terminated ڿ ־ SQL Ǹ Ѵ.	|
-------------------------------------------------------------------------------------------------
| mysql_real_connect()		| MySQL  Ѵ.					|
-------------------------------------------------------------------------------------------------
| mysql_real_query()		| Counted ڿ ־ SQL Ǹ Ѵ.			|
-------------------------------------------------------------------------------------------------
| mysql_reload()		|  Ͽ grant ̺ ٽ εϰ Ѵ.		|
-------------------------------------------------------------------------------------------------
| mysql_row_seek()		| mysql_row_tell() Լ       	|
|				|  ã´.							|
-------------------------------------------------------------------------------------------------
| mysql_row_tell()		| Row cursor ġ Ѵ.					|
-------------------------------------------------------------------------------------------------
| mysql_select_db()		| Ư DB Ѵ.						|
-------------------------------------------------------------------------------------------------
| mysql_shutdown()		| DB  ˴ٿŲ.					|
-------------------------------------------------------------------------------------------------
| mysql_stat()			|  ¸ ǥϴ ڿ Ѵ.			|
-------------------------------------------------------------------------------------------------
| mysql_store_result()		| ü  Ŭ̾Ʈ ´.				|
-------------------------------------------------------------------------------------------------
| mysql_thread_id()		|   ID Ѵ.					|
-------------------------------------------------------------------------------------------------
| mysql_use_result()		|  ͸ Ѱܹޱ    ʱȭѴ.		|
-------------------------------------------------------------------------------------------------

    mysql_init() Լ ȣν connection handler ʱȭ , 
 handler hostname, user name, password  ٸ   mysql_real_connect() 
Լ ȣѴ.  ۾  mysql_close() Լ ȣν  Ѵ.

 ̷ ִ  Ŭ̾Ʈ mysql_query() mysql_real_query() Լ ̿
  SQL Ǹ   ִ. mysql_query() ǰ null-terminated string̾ 
, mysql_real_query() counted string̾ Ѵ.  string ̳ʸ (߰ 
NULL Ʈ   ִ) 쿡 ݵ mysql_real_query() Ͽ Ѵ.

SELECT ǰ ƴ (INSERT, UPDATE, DELETE) ؼ mysql_affected_row() Լ
 ȣν 󸶳   affectǾ   ִ.

SELECT  , selectǾ  (result set)  ȴ(SHOW, DESCRIBE, 
EXPLAIN    ϴ Ϳ ־ SELECT ϸ, ̵ SELECT 
 ϰ óǾ Ѵ)

Ŭ̾Ʈ  óϴ  ΰ ִ. ϳ mysql_store_result() Լ
 ȣν ü   ѹ  ν,  Լ ǿ  ϵ 
  κ  Ŭ̾Ʈ Ѵ. ι° mysql_use_result() Լ
 ȣν Ŭ̾Ʈ ະ   ̴.  Լ   
۾ ʱȭϱ⸸ ϸ  κ   ʴ´.

 ΰ  , mysql_fetch_row() Լ ȣν 鿡 ϰ ȴ. 
mysql_store_result()  mysql_fetch_row() κ ̹ fetch 鿡 ϸ, 
mysql_use_result() , mysql_fetch_row()  κ  ´.   
  ũ⿡   mysql_fetch_lengths() Լ ȣν   ִ.

  ̿ ۾  mysql_free_result() ȣν     
޸𸮸 Ѵ.

 ΰ retrieval ü ȣ ̴. Ŭ̾Ʈ α׷  䱸  
   Ͽ Ѵ. δ mysql_store_result() ַ ȴ.

mysql_store_result()   Ŭ̾Ʈ fetch Ŀ Ŭ̾Ʈ 鿡 
    Ӹ ƴ϶ mysql_data_seek() mysql_row_seek() ̿Ͽ 
  ȿ  ġ(current row position) ν    յڷ  
 ִٴ ̴.  mysql_num_rows() ȣν 󸶳   ֳ  
 ִ. ٸ , mysql_store_result()  䱸Ǵ ޸𸮰   out-of-
memory ¸  ɼ . 

mysql_use_result()  ѹ ϳ ุ ϱ     ޸
  ʿϴٴ ̸    ޸ Ҵ Ϸ Ͽ  ӵ   
.   tying upϴ  ϱ Ͽ    ó־ ϸ 
   ϰ 鿡     θ   ִ.  
  ޱ     󸶳   ֳ   .   
 ã ϴ  ãҴ    ް ־ Ѵ.

API ̿ν ǰ SELECTΰ ƴѰ  ¿ Ŭ̾Ʈ ǿ 
   ְ   ִµ, ̴ mysql_query() (Ȥ mysql_real_query()) ȣ 
Ŀ mysql_store_result() ȣν ϴ.    (succeed)̸ ǰ 
SELECT̸    ִ.  ̸ mysql_num_fields() ȣϿ result 
 Ǿ°   , mysql_num_fields() 0 ϸ Ǵ ƹ 
͵  ʴ´(̰ ǰ INSERT, UPDATE, DELETE  ǹѴ).  
mysql_num_fields() 0 ƴϸ Ǵ ݵ  Ͽ ϴµ  ʾ 
ǹϸ, ̴ ǰ SELECT ࿡ Ͽ ˷ش. 

mysql_store_result() mysql_use_result()   ϴ ʵ鿡  (ʵ 
, ̸,  )   ְ ش. mysql_fetch_field()  ȣϿ   
ʵ   ϰų, mysql_fetch_field_direct() ȣν    
ʵ ȣ Ͽ   ִ.  ʵ Ŀ ġ(current field cursor position) 
mysql_field_seek() Լ ȣν   ִ. Setting the field cursor affects subsequent 
calls to mysql_fetch_field().  mysql_fetch_fields() ȣν Ѳ  ʵ 
   ִ. 

 ã ϱ ؼ MySQL mysql_errno() mysql_error() Լ Ͽ  
   ְ ش.   Լ  ֱٿ invoke ϰų   
ִ Լ   ڵ Ȥ  ޽ ϸ, ̸ Ͽ   ߻
ϰ װ ΰ   ִ.


18.4 C API function descriptions

Ʒ Լ  ־ NULL ǥ μ ϰ C α׷  
NULL ǹ̸, MySQL NULL  ǹϴ  ƴϴ. 

  ϴ Լ  ͳ  Ѵ.   ٸ ǥð ٸ 
͸ ϴ Լ Ͽ  NULL ƴ ,  ǥϱ ؼ 
NULL  Ѵ.  ϴ Լ 쿡 Ͽ  0,  
 0 ƴ  Ѵ. 

Լ   쿡 mysql_errno(), mysql_error() Լ ̿Ͽ  üũ 
 ִ. 

18.4.1 mysql_affected_rows()
my_ulonglong mysql_affected_rows(MYSQL *mysql) 

18.4.1.1 Description
  UPDATE, DELETE, INSERT ǿ    ϸ, ַ 
UPDATE, DELETE, INSERT   mysql_query() Լ ȣ   ȣȴ. 
SELECT   mysql_affected_rows() Լ mysql_num_row() Լó Ѵ.

mysql_affected_rows() Լ  ũ(macro) Ǿ ִ.

18.4.1.2 Return values
0 ū  affectedǰų retrieved   ǹѴ. 0 WHERE ǿ մ
ϴ ڵ尡 ų ƹ ǵ   ʾ ǹѴ. -1 ǰ  
Ͽų Ȥ SELECT   mysql_affected_rows() Լ mysql_store_result() 
 ȣ  ȣǾ ǹѴ.

18.4.1.3 Errors
.

18.4.1.4 Example
mysql_query(&mysql,"UPDATE products SET cost=cost*1.25 WHERE group=10");
printf("%d products updated",mysql_affected_rows(&mysql));


18.4.2 mysql_close()
void mysql_close(MYSQL *mysql) 

18.4.2.1 Description
 ִ   ´.   ڵ(connection handle) mysql_init()̳ 
mysql_real_connect() ؼ ڵ ҴǾٸ mysql_close() mysql   
 ڵ deallocationѴ.

18.4.2.2 Return values
. 

18.4.2.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order.
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.3 mysql_connect()
MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd) 

18.4.3.1 Description
 Լ ϴ   ,  mysql_real_connect() Լ ַ ȴ.

mysql_connect() Լ ȣƮ ư ִ MySQL DB   õѴ. 
Mysql_connect() mysql_get_client_info() Լ  ٸ  API Լ ϱ 
 ݵ  Ǿ Ѵ. 

Լ μ  ǹ̴ mysql_real_connect() Լ ϴ. 

18.4.3.2 Return values
mysql_real_connect() Լ . 

18.4.3.3 Errors
mysql_real_connect() Լ . 


18.4.4 mysql_create_db()
int mysql_create_db(MYSQL *mysql, const char *db) 

18.4.4.1 Description
'db' μ ־ ̸ ͺ̽ Ѵ.

 Լ ϴ   ,  'CREATE DATABASE'  SQL  
mysql_query() Լ ̿Ͽ ϴ  .
 
18.4.4.2 Return values
ͺ̽    0,  ߻  0 ƴ  Ѵ. 

18.4.4.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.4.4 Example
if(mysql_create_db(&mysql, "my_database"))
{
   fprintf(stderr, "Failed to create new database.  Error: %s\n", mysql_error(&mysql));
}


18.4.5 mysql_data_seek()
void mysql_data_seek(MYSQL_RES *result, unsigned int offset) 

18.4.5.1 Description
     ã´. ̸ ؼ   ü ǿ  ü 
 ϰ ־ ϸ,  mysql_data_seek() Լ mysql_use_result() Լʹ 
   mysql_store_result() Լ Ѵ. 

Offset μ 0 mysql_num_rows(result)-1    Ѵ.

18.4.5.2 Return values
None. 

18.4.5.3 Errors
None. 


18.4.6 mysql_debug()
void mysql_debug(char *debug) 

18.4.6.1 Description
debug μ ־ ڿ  DBUG_PUSH Ѵ. mysql_debug() Լ Fred 
Fish debug ̺귯 Ѵ.  Լ ϱ ؼ ݵ Ŭ̾Ʈ ̺
  ϵ Ͽ Ѵ. 19.10  MySQL  뿡 
 ϸ, 19.11 MySQL Ŭ̾Ʈ 뿡 Ͽ Ѵ.

18.4.6.2 Return values
None. 

18.4.6.3 Errors
None. 

18.4.6.4 Example
Ʒ  ȣ Ŭ̾Ʈ ̺귯 Ͽ Ŭ̾Ʈǿ '/tmp/client.trace' 
 trace file ϰ ش:

mysql_debug("d:t:O,/tmp/client.trace");


18.4.7 mysql_drop_db()
int mysql_drop_db(MYSQL *mysql, const char *db) 

18.4.7.1 Description
db μ ־ ̸ ͺ̽ ӽŲ. 

 Լ ϴ   ,  'DROP DATABASE'  SQL  
mysql_query() Լ ̿Ͽ ϴ  .

18.4.7.2 Return values
ͺ̽  ӵ  0,  ߻  0 ƴ  Ѵ.

18.4.7.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.7.4 Example
if(mysql_drop_db(&mysql, "my_database"))
  fprintf(stderr, "Failed to drop the database: Error: %s\n", mysql_error(&mysql));


18.4.8 mysql_dump_debug_info()
int mysql_dump_debug_info(MYSQL *mysql) 

18.4.8.1 Description
 Ͽ α׿   ϰ Ѵ. Ǿ ִ (connected user) 
 ۾    ־ Ѵ.

18.4.8.2 Return values
 ϸ 0,  ߻ϸ 0 ƴ  Ѵ.

18.4.8.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.9 mysql_eof()
my_bool mysql_eof(MYSQL_RES *result) 

18.4.9.1 Description
 Լ ϴ   ,  mysql_errno() Լ mysql_error() Լ 
Ѵ.
 
mysql_eof() Լ      ǴѴ.

mysql_store_result() Լκ    ٸ Ŭ̾Ʈ ѹ ۾
 ü  Եȴ.   mysql_fetch_row() Լκ ϵǴ NULL ׻  
  Ͽ ǹϰ Ǹ mysql_eof() Լ ȣ ʿ䰡 .

 mysql_use_result() Լ Ͽ   ٸ   
mysql_fetch_row() Լ ȣ  κ ϳ .   ߿  
 ߻  ֱ  myql_fetch_row() Լ ϴ NULL  ݵ  
  Ͽ ǹѴٰ   .   mysql_eof() Լ Ͽ 
 Ͼ   ִ. ̶ mysql_eof() Լ     
 0 ƴ  ϸ,  쿡 0 ϰ ȴ. 

ǥ mySQL  Լ mysql_errno() mysql_error() Լ    ϱ 
 ̵ ϴ  ١


18.4.9.2 Return values
̸ 0,    Ͽ 0 ƴ  Ѵ.

18.4.9.3 Errors
None. 

18.4.9.4 Example
 mysql_eof() 뿹 ش:

mysql_query(&mysql,"SELECT * FROM some_table");
result = mysql_use_result(&mysql);
while((row = mysql_fetch_row(result)))
{
    // do something with data
}
if(!mysql_eof(result))  // mysql_fetch_row() failed due to an error
{
    fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
}

However, you can achieve the same effect with the standard MySQL error functions: 

mysql_query(&mysql,"SELECT * FROM some_table");
result = mysql_use_result(&mysql);
while((row = mysql_fetch_row(result)))
{
    // do something with data
}
if(mysql_errno(&mysql))  // mysql_fetch_row() failed due to an error
{
    fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
}


18.4.10 mysql_errno()
unsigned int mysql_errno(MYSQL *mysql) 

18.4.10.1 Description
mysql μ   ῡ Ͽ mysql_errno()  ֱٿ  API Լ 
  ڵ带 Ѵ. 0 ϸ ƹ  ߻  ǹѴ. Ŭ̾
Ʈ  ޽ ѹ MYSQL 'errmsg.h'  Ͽ Ǿ ,   
 ѹ 'mysqld_error.h' Ͽ ִ.

18.4.10.2 Return values:
 ڵ  . ƹ   0 Ѵ.

18.4.10.3 Errors
None. 


18.4.11 mysql_error()
char *mysql_error(MYSQL *mysql) 

18.4.11.1 Description
mysql μ   ῡ Ͽ mysql_error()  ֱٿ  API Լ 
  ޽ Ѵ.  ִ ڿ("") ϸ ƹ  ߻ 
 ǹѴ. ̰  ΰ ׽Ʈ  ǹѴ:

if(mysql_errno(&mysql)) {
    // an error occurred
}

if(mysql_error(&mysql)[0] != '\0') {
    // an error occurred
}

Ŭ̾Ʈ  ޽  MySQL Ŭ̾Ʈ ̺귯 ν 
  ִ.   ٸ    ޽   , 9.1  
MySQL  Ǵ  ٷ.

18.4.11.2 Return values
 ϴ ڿ.   쿡 ִ ڿ Ѵ.

18.4.11.3 Errors
None. 


18.4.12 mysql_escape_string()
unsigned int mysql_escape_string(char *to, const char *from, unsigned int length) 

18.4.12.1 Description
from μ ִ ڿ  Ѱ escaped SQL  ڵϿ to μ ִ´. 
ڵǾ ڵ NUL(ASCII 0), `\n', `\r', `\', `'' ̴(7.1 ڿ ڵ 
ϴ  Ͽ ٷ).

from μ ڿ length μ ־ Ʈ ŭ (terminating NULL 
)  Ѵ. to μ ݵ  length μ ־  2迡 1 
 ŭ Ʈ ҴǾ Ѵ. mysql_escape_string() Լ   to μ 
 null-terminated ڿ ȴ. ϰ ڵǾ ڿ ̸, ̰ 
terminating null ڰ Ե  ̴.

to μ  ڿ null-terminated ڿ  ڿ strlen() Լ 
strcpy() Լ Ͽ ȵȴ.  from μ ־ ڿ null ϰ 
ִٸ mysql_escape_string() Լ  ڸ '\' տ ٿ to μ ְ Ǹ, ̰
  Լ  terminating null νϰ ȴ.

 ̷  null Ʈ mysql_query() ؼ terminating null  
 Ǹ ϰ    ȴ.  mysql_escape_query() Լ ̿Ͽ 
Ǹ   mysql_query() Լ ϱ ٴ mysql_real_query() Լ ϴ 
 .

18.4.12.2 Example
char query[1000],*end;

end = strmov(query, "INSERT INTO test_table values("); 
*end++ = '\";
end += mysql_escape_string(query,"What's this",11);
*end++ = '\";
*end++ = ',';
*end++ = '\";
end += mysql_escape_string(query,"binary data: \0\r\n",16);
*end++ = '\";
*end++ = ')';

if (mysql_real_query(&mysql,query,(int) (end - query))) {
   fprintf(stderr, "Failed to insert row, Error: %s\n",
           mysql_error(&mysql));
}

  strmov() Լ mysqlclient ̺귯 ԵǾ , strcpy() Լ 
ϰ   ù° μ terminating null  ͸ ϴ  
. 

18.4.12.3 Return values
terminating null ڸ  to μ   

18.4.12.4 Errors
None. 


18.4.13 mysql_fetch_field()
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result) 

18.4.13.1 Description
   ÷ (definition) MYSQL_FIELD ü Ƽ Ѵ.  
  ÷鿡    ϴ 쿡  Լ ݺϿ ȣѴ.  
 ʵ尡   mysql_fetch_field() Լ NULL Ѵ.

ο SELECT ǰ   mysql_fetch_field() ο ù° ʵ忡  
 ϱ  ʱȭȴ. mysql_fetch_field() Լ  ϵǴ ʵ  
mysql_field_seek() Լ ȣ⿡   ޴´.

SELECT  ϱ  msyql_query() ȣϿ mysql_store_result() ȣ 
 , mysql_fetch_field() ȣϿ BLOB ʵ ̸ ûϰ Ǹ MySQL 
Ʈ blob  8K Ʈ Ѵ. ϴ ѹ   Ǹ field->max_length
 ־ ǿ ִ ÷  ū  ̸  ȴ.

18.4.13.2 Return values
 ÷  MYSQL_FIELD ü ϸ,  ̻  ʵ尡  NULL 
Ѵ. 

18.4.13.3 Errors
None. 

18.4.13.4 Example
MYSQL_FIELD *field;

while((field = mysql_fetch_field(result)))
{
    printf("field name %s\n", field->name);
}


18.4.14 mysql_fetch_fields()
MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result) 

18.4.14.1 Description
 ¿   MYSQL_FIELD ü 迭 Ѵ.  ü   
  ÷鿡  ʵ ǿ    ִ. 

18.4.14.2 Return values
   ÷鿡  MYSQL_FIELD ü 迭

18.4.14.3 Errors
None. 

18.4.14.4 Example
unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *fields;

num_fields = mysql_num_fields(result);
fields = mysql_fetch_fields(result);
for(i = 0; i < num_fields; i++) {
   printf("Field %u is %s\n", i, fields[i].name);
}


18.4.15 mysql_fetch_field_direct()
MYSQL_FIELD *mysql_fetch_field_direct(MYSQL_RES *result, unsigned int fieldnr) 

18.4.15.1 Description
   fieldnr μ ־ ʵ ȣ ʵ忡  ʵ Ǹ MYSQL_FIELD 
ü Ѵ.  Լ ̿Ͽ  ÷  Ǹ   ִ. fieldnr 
 0 mysql_num_fields(result)-1    Ѵ.

18.4.15.2 Return values
 ʵ忡  MYSQL_FIELD ü

18.4.15.3 Errors
None. 

18.4.15.4 Example
unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *field;

num_fields = mysql_num_fields(result);
for(i = 0; i < num_fields; i++) {
    field = mysql_fetch_field_direct(result, i);
    printf("Field %u is %s\n", i, field->name);
}


18.4.16 mysql_fetch_lengths()
unsigned long *mysql_fetch_lengths(MYSQL_RES *result) 

18.4.16.1 Description
     ÷ ̸ Ѵ.  ʵ  ϰ ϴ 
    ȭ(optimization)  ϰ   ִ. strlen() Լ ȣ
 ʿ䰡  ̴. Ư    ͸ ϰ ִ 쿡 strlen() 
Լ NULL ڸ ϰ ִ ʵ忡 Ͽ ߸    ֱ  
 ũ⸦ ϴµ  Լ Ͽ Ѵ.

 ÷̳ NULL   ÷  ̴ 0 ȴ.  ΰ 츦 ϴ 
 mysql_fetch_row() Լ   Ѵ.

18.4.16.2 Return values
 ÷(null termination character ) ũ⸦  ִ unsigned long  
.  ߻ 쿡 NULL Ѵ.

18.4.16.3 Errors
mysql_fetch_lengths()    ࿡ ؼ ȿϴ. mysql_fetch_row() Լ ȣ
ϱ   Լ ȣϰų       Ŀ ȣϴ 쿡 
NULL Ѵ.

18.4.16.4 Example
MYSQL_ROW row;
unsigned long *lengths;
unsigned int num_fields;
unsigned int i;

row = mysql_fetch_row(result);
if (row) {
    num_fields = mysql_num_fields(result);
    lengths = mysql_fetch_lengths(result);
    for(i = 0; i < num_fields; i++) {
         printf("Column %u is %lu bytes in length.\n", i, lengths[i]);
    }
}


18.4.17 mysql_fetch_row()
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result) 

18.4.17.1 Description
    ´. mysql_store_result() Լ ȣ Ŀ  ,  
    mysql_fetch_row() NULL Ѵ. mysql_use_result() Լ 
ȣ Ŀ  ,  ̻   ų  ߻Ͽ  NULL 
Ѵ.

    mysql_num_fields(result)  ־. mysql_fetch_row() ȣ
 Ͽ       row[0] row[mysql_num_fields(result)-1]
    ִ.  ο NULL  ִ 쿡 NULL ͷ ǥõȴ. 

  ʵ  ̵ mysql_fetch_lengths() Լ ȣν   մ. 
ִ ʵ峪 NULL ϴ ʵ  0 ̸ , ʵ   
͸ üũν  ΰ 츦   ִ.  Ͱ NULL̸ ش 
尡 NULL ̸, ƴϸ ִ ʵ ǹѴ.

18.4.17.2 Return values
 ࿡  MYSQL_ROW ü.  ̻   ų  ߻  
NULL Ѵ.
 
18.4.17.3 Errors
CR_SERVER_LOST 
The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
An unknown error occurred. 

18.4.17.4 Example
MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;

num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result))) {
   unsigned long *lengths;
   lengths = mysql_fetch_lengths(result);
   for(i = 0; i < num_fields; i++) {
       printf("[%.*s] ", (int) lengths[i], row[i] ? row[i] : "NULL");
   }
   printf("\n");
}


18.4.18 mysql_field_seek()
MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset) 

18.4.18.1 Description
ʵ Ŀ offset μ ־ ġ Ѵ.  mysql_fetch_field() Լ ȣ 
ش offset ̵ ŭ شǴ ÷  ʵ   ȴ.

 ùκ  ؼ offset 0 ְ Լ ȣϸ ȴ.

18.4.18.2 Return values
ʵ Ŀ  (previous value)

18.4.18.3 Errors
None. 


18.4.19 mysql_field_tell()
MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result) 

18.4.19.1 Description
 mysql_fetch_field() Լ   ʵ Ŀ ġ ˷ش.  ϰ 
mysql_field_seek() Լ offset μ   ִ.

18.4.19.2 Return values
ʵ Ŀ  (current offset)

18.4.19.3 Errors
None. 


18.4.20 mysql_free_result()
void mysql_free_result(MYSQL_RES *result) 

18.4.20.1 Description
mysql_store_result(), mysql_use_result(), mysql_list_dbs()     ҴǾ 
޸𸮸 Ѵ.    ۾   ݵ mysql_free_result() Լ 
ȣϿ ޸𸮸  ־ Ѵ.

18.4.20.2 Return values
None. 

18.4.20.3 Errors
None. 


18.4.21 mysql_get_client_info()
char *mysql_get_client_info(void) 

18.4.21.1 Description
Ŭ̾Ʈ ̺귯 (client library version)   ǥϴ ڿ Ѵ.

18.4.21.2 Return values
MySQL client library version ǥϴ ڿ

18.4.21.3 Errors
None. 


18.4.22 mysql_get_host_info()
char *mysql_get_host_info(MYSQL *mysql) 

18.4.22.1 Description
 ȣƮ  ϴ  ǰ ִ (connection) (type) ϴ 
ڿ Ѵ.

18.4.22.2 Return values
 ȣƮ (server host name)  (connection type) Ÿ ڿ 

18.4.22.3 Errors
None. 


18.4.23 mysql_get_proto_info()
unsigned int mysql_get_proto_info(MYSQL *mysql) 

18.4.23.1 Description
 ῡ ǰ ִ   Ѵ.

18.4.23.2 Return values
 ῡ ǰ ִ   Ÿ unsigned integer

18.4.23.3 Errors
None. 


18.4.24 mysql_get_server_info()
char *mysql_get_server_info(MYSQL *mysql) 

18.4.24.1 Description
  ѹ(server version number) ǥϴ ڿ Ѵ.

18.4.24.2 Return values
  ѹ ǥϴ ڿ

18.4.24.3 Errors
None. 


18.4.25 mysql_info()
char *mysql_info(MYSQL *mysql) 

18.4.25.1 Description
 ֱٿ   peogks  ϴ ڿ Ѵ. ڿ (format)
  ŸԿ  ޶, Ʒ . ڿ ǿ    
 ȴ.

INSERT INTO ... SELECT ... 
String format: Records: 100 Duplicates: 0 Warnings: 0 
INSERT INTO ... VALUES (...),(...),(...)... 
String format: Records: 3 Duplicates: 0 Warnings: 0 
LOAD DATA INFILE ... 
String format: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 
ALTER TABLE 
String format: Records: 3 Duplicates: 0 Warnings: 0 

18.4.25.2 Return values
 ֱٿ  ǿ  ΰ  ǥϴ 忭. ش ǿ Ͽ 
      NULL Ѵ.

18.4.25.3 Errors
None. 


18.4.26 mysql_init()
MYSQL *mysql_init(MYSQL *mysql) 

18.4.26.1 Description
mysql_real_connect() Լ  MYSQL ü Ҵ(allocate)ϰų ʱȭ(initialize)Ѵ. 
 mysql μ NULL ̸ mysql_init() Լ ο ü Ҵ, ʱȭϿ 
 ü Ѵ. NULL Ͱ ƴϸ ü ʱȭ  ü ּҰ ϵȴ. 
 mysql_init() Լ ο ü Ҵϰ Ǹ  ü  ݱ  
mysql_close() Լ ȣ  ǰ ȴ.

18.4.26.2 Return values
ʱȭ MYSQL * handle. ο ü Ҵϱ  ޸𸮰   NULL 
Ѵ.

18.4.26.3 Errors
޸𸮰   NULL ϵȴ.


18.4.27 mysql_insert_id()
my_ulonglong mysql_insert_id(MYSQL *mysql) 

18.4.27.1 Description
AUTO_INCREMENT ʵ忡 Ͽ  ֱٿ  ID Ѵ.  Լ 
AUTO_INCREMENT ʵ带 ϴ ̺ INSERT Ǹ  Ŀ ȴ.

18.4.27.2 Return values
 ֱٿ ŵ AUTO_INCREMENT ʵ 

18.4.27.3 Errors
None. 


18.4.28 mysql_kill()
int mysql_kill(MYSQL *mysql, unsigned long pid) 

18.4.28.1 Description
 Ͽ pid μ ־ (thread) ̵(kill) ûѴ.

18.4.28.2 Return values
  0,  ߻  0 ƴ  Ѵ.

18.4.28.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.29 mysql_list_dbs()
MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild) 

18.4.29.1 Description
wild μ   Խ(simple regular expression) ġǴ   ͺ
̽ ̸ ϰ ִ   Ѵ. wild μ '%', '_'  ϵī 
ڸ     ͺ̽ ̸  ϴ  NULL   
. mysql_list_dbs() Լ ȣϴ  'SHOW databases [LIKE wild]'  Ǹ 
 Ͱ ϴ.

ݵ mysql_free_result() Լ ̿Ͽ    ־ Ѵ.

18.4.29.2 Return values
  MYSQL_RES   ϸ,   NULL Ѵ.

18.4.29.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_OUT_OF_MEMORY 
	Out of memory. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.30 mysql_list_fields()
MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild) 

18.4.30.1 Description
wild μ   Խ(simple regular expression) ġǴ ̺  ʵ
 ϰ ִ   Ѵ. wild μ '%', '_'  ϵī ڸ 
    ʵ  ϴ  NULL   ִ. mysql_list_fields() 
Լ ȣϴ  'SHOW fields FROM table [LIKE wild]'  Ǹ ϴ Ͱ 
ϴ.

ݵ mysql_free_result() Լ ̿Ͽ    ־ Ѵ.

18.4.30.2 Return values
  MYSQL_RES   ϸ,   NULL Ѵ.

18.4.30.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.31 mysql_list_processes()
MYSQL_RES *mysql_list_processes(MYSQL *mysql) 

18.4.31.1 Description
   ϴ   Ѵ. ̰ 'mysqladmin processlist' 
    ش.
ݵ mysql_free_result() Լ ̿Ͽ    ־ Ѵ.

18.4.31.2 Return values
  MYSQL_RES   ϸ,   NULL Ѵ.

18.4.31.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.32 mysql_list_tables()
MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild) 

18.4.32.1 Description
wild μ   Խ(simple regular expression) ġǴ  ͺ̽ 
 ̺ ϰ ִ   Ѵ. wild μ '%', '_'  ϵī
 ڸ     ̺  ϴ  NULL   ִ. 
mysql_list_tables() Լ ȣϴ  'SHOW tables [LIKE wild]'  Ǹ ϴ 
Ͱ ϴ.

ݵ mysql_free_result() Լ ̿Ͽ    ־ Ѵ.

18.4.32.2 Return values
  MYSQL_RES   ϸ,   NULL Ѵ.

18.4.32.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.33 mysql_num_fields()
unsigned int mysql_num_fields(MYSQL_RES *result) 
Ȥ,
unsigned int mysql_num_fields(MYSQL *mysql) 

18.4.33.1 Description
   ÷  Ѵ.

mysql_num_fields() ȣ   ¿   Ȥ  ڵ  ȣ  
ִ.  mysql_store_result() Լ NULL  (   ͸   
 )  ڵ ϰ Ǹ,   mysql_num_fields() Լ ȣν 
mysql_store_result() Լ   (non-empty result) Ͽ θ 
  ִ. ̰ Ŭ̾Ʈ α׷ Ͽ ǰ SELECT (Ȥ SELECT-like) 
ΰ ƴѰ  ϴ ¿  ൿ   ֵ ش. Ʒ  
ִ.

18.4.33.2 Return values
   ʵ  ǥϴ unsigned integer.

18.4.33.3 Errors
None. 

18.4.33.4 Example
MYSQL_RES *result;
unsigned int num_fields;
unsigned int num_rows;

if (mysql_query(&mysql,query_string)) {
    // error
}
else {   // query succeeded, process any data returned by it 
    result = mysql_store_result(&mysql);
    if (result) {   // there are rows
          num_fields = mysql_num_fields(result);
        // retrieve rows, then call mysql_free_result(result)
    }
    else {   // mysql_store_result() returned nothing; should it have?
        if(mysql_num_fields(&mysql) == 0) {
            // query does not return data
            // (it was not a SELECT)
            num_rows = mysql_affected_rows(&mysql);
        }
        else {   // mysql_store_result() should have returned data
            fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
        }
    }
}

 ٸ  mysql_num_fields(&mysql) ſ mysql_errno(&mysql) ȣϴ ̴. 
 , ǰ SELECT ΰ msyql_num_fields() ϰ ϱ⺸ٴ 
mysql_store_result()κ   üũϰ ȴ.


18.4.34 mysql_num_rows()
my_ulonglong mysql_num_rows(MYSQL_RES *result) 

18.4.34.1 Description
     Ѵ.

mysql_num_rows() Լ  δ    ؼ mysql_store_result() Լ 
ϴ°  mysql_use_result() Լ ϴ° ޷ִ.  mysql_store_result() Լ 
Ѵٸ mysql_num_rows() Լ  ȣ  ִ.  mysql_use_result() Լ 
Ѵٸ mysql_num_rows() Լ      retrieveǱ  ù
   Ѵ.

18.4.34.2 Return values
    
 
18.4.34.3 Errors
None. 


18.4.35 mysql_ping()
int mysql_ping(MYSQL *mysql) 

18.4.35.1 Description
    ϰ ֳ Ȯϸ,    쿡 ڵ
 翬 õѴ.

 Լ Ŭ̾Ʈ ʹ        üũϰ 
ϴٸ ٽ ϱ    ִ.

18.4.35.2 Return values
  0,   0 ƴ  Ѵ.

18.4.35.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.36 mysql_query()
int mysql_query(MYSQL *mysql, const char *query) 

18.4.36.1 Description
null-terminated ڿ query μ  SQL Ǹ Ѵ. Ǵ ݵ ϳ 
SQL  ̷ ϸ, ǹ ݷ(;)̳ '\g' ٿ ȵȴ.

mysql_query()  ͸ ϴ ǿ   .  ʹ ߰ 
NULL ڸ    ̰ mysql_query() Լ ű⼭  ڿ  
 νϱ  ̰쿡 mysql_real_query() Լ Ͽ Ѵ.

18.4.36.2 Return values
̸ 0, ̸ 0 ƴ  .

18.4.36.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.37 mysql_real_connect()
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, 
const char *db, uint port, const char *unix_socket, uint client_flag) 

18.4.37.1 Description
mysql_real_connect() Լ ȣƮ ư ִ MySQL ͺ̽   
õѴ. mysql_real_connect() mysql_get_client_info() Լ  ٸ  API Լ 
ϱ  ݵ  Ǿ Ѵ. 

ϴ μ  :

mysql μ MYSQL connection ṵ̈ų NULL   ִ.  mysql μ 
NULL̸ C API ڵ  ü  ޸𸮸 Ҵϰ mysql_close() ȣ
  ̸ Ѵ. ̰      ޽   ٴ 
̴(mysql_errno() mysql_error() Լκ  ޽  ؼ ȿ 
MYSQL ͸ Ͽ Ѵ). mysql μ NULL ƴϸ ̹ ϴ MYSQL 
ü ּҰ Ǿ Ѵ.  , mysql_real_connect() Լ ȣϱ  ݵ 
mysql_init() Լ ȣϿ MYSQL ü ʱȭ ־ Ѵ.   .
host μ ȣƮ ǰų IP 巹   ִ.  host μ NULL̰ų 
"localhost"   ȣƮ  ֵȴ.  OS (socket-Unix)̳ 
(pipe-Win32) Ѵٸ   Ͽ TCP/IP  ̵   ִ.
user μ  MySQL α ̵ ־ش. User μ NULL̸  
ڷ ֵȴ. Unix ȯ濡  ý αθ Ǹ, Windows ODBC 
 ڸ Ȯ Ǿ Ѵ. 15.4 ODBC  α׷  ʵ
  ä ΰ Ͽ Ѵ. 
passwd μ ڿ  н带 ־ش.  passwd NULL̸ user 
 н ʵ尡 ִ ڵ鸸 ġ  üũǾ. ̰ 
̽ ڷ Ͽ ͺ̽ ڰ ڽ н带 Ͽ  
   ϴ MySQL  ý   ֵ ش.   
mysql_real_connect() Լ ȣϱ  н带 ȣȭϷ  ƾ Ѵٴ 
̸, н ȣȭ Ŭ̾Ʈ API Ͽ ڵ ȴ.
db μ  ͺ̽ ̸ ־ش.  db μ NULL̸ Ʈ 
ͺ̽  õϰ ȴ. 
port μ 0 ƴϸ port μ õ Ʈ ȣ TCP/IP   Ʈ ȣ 
ϰ ȴ. host μ   Ѵٴ Ϳ Ѵ. 
unix_socket μ NULL ƴϸ  μ ڿ  socket̳ named pipe ȴ. 
client_flag   0 ſ Ư 쿡  ÷׵    
ִ. 

-----------------------------------------------------------------------------------------
| Flag name	    | Flag meaning 							|
-----------------------------------------------------------------------------------------
| CLIENT_FOUND_ROWS | affected row  ƴ found row  Ѵ.		|
-----------------------------------------------------------------------------------------
| CLIENT_NO_SCHEMA  | db_name.tbl_name.col_name  (syntax)  ʴ´.	|
|		    | ̰ ODBC  μ,  ̿   ϸ ļ|
|		    |  ߻Ű ȴ. ̰ ODBC α׷ ׸ ãƳ  |
|		    | ϰ   ִ.						|
-----------------------------------------------------------------------------------------
| CLIENT_COMPRESS   |  (compression protocol) Ѵ.			|
-----------------------------------------------------------------------------------------
| CLIENT_ODBC	    | Ŭ̾Ʈ ODBC Ŭ̾Ʈ ˷ָ, ̰ mysqld  	|
|		    | ODBC ϵ ش.						|
-----------------------------------------------------------------------------------------

18.4.37.2 Return values
 ϸ MYSQL *  ڵ(connection handle) ϸ, ῡ ϸ NULL
 Ѵ.      ù° μ NULL  ʾҴٸ  
μ ϴ.

18.4.37.3 Errors
CR_CONN_HOST_ERROR 
	MySQL  ῡ  
CR_CONNECTION_ERROR 
	 MySQL  ῡ  
CR_IPSOCK_ERROR 
	IP    
CR_OUT_OF_MEMORY 
	޸𸮰 (Out of memory). 
CR_SOCKET_CREATE_ERROR 
	Unix    
CR_UNKNOWN_HOST 
	ش ȣƮ  IP ּҸ ã  .
CR_VERSION_ERROR 
	ġ ʴ   ϴ Ŭ̾Ʈ ̺귯   
	Ϸ õ Ͽ  ġ ߻. ̰ ſ  Ŭ̾Ʈ 
	̺귯 Ͽ ο   ϰ ϴ 쿡 ߻Ѵ. ̷ 
 	쿡  õ  '--old-protocol' ɼ ־ õϸ ذ  ִ.
CR_NAMEDPIPEOPEN_ERROR; 
	Win32 ȯ濡 named pipe ϴµ . 
CR_NAMEDPIPEWAIT_ERROR; 
	Win32 ȯ濡 named pipe ٸµ . 
CR_NAMEDPIPESETSTATE_ERROR; 
	Win32 ȯ濡 pipe ڵ鷯 µ . 

18.4.37.4 Example
MYSQL mysql;

mysql_init(&mysql);
if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0)) {
    fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql));
}


18.4.38 mysql_real_query()
int mysql_real_query(MYSQL *mysql, const char *query, unsigned int length) 

18.4.38.1 Description
query μ  SQL Ǹ ϸ, query μ length μ  ̸ 
 Ѵ. Ǵ ϳ SQL  ̷ ϸ, ݷ̳ '\g' SQL ٿ
 ȵȴ.

 ͸ ϴ    mysql_query() Լ ƴ mysql_real_query() 
Լ Ͽ Ѵ.  mysql_real_query()Լ ǹ ̸   strlen() 
Լ  ʱ  mysql_query() Լ Ͽ  ӵ .

18.4.38.2 Return values
ǿ ϸ 0,  ߻ϸ 0 ƴ  Ѵ.

18.4.38.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.39 mysql_reload()
int mysql_reload(MYSQL *mysql) 

18.4.39.1 Description
MySQL  Ͽ grant ̺ ٽ е ûѴ.  Ǿ ִ ڴ 
ݵ   ٽ о Ѵ.

 Լ ϴ   ,  'FLUSH PRIVILEGES' SQL  
mysql_query() Լ ̿Ͽ ϴ  .

18.4.39.2 Return values
ϸ 0,  0 ƴ  Ѵ.
 
18.4.39.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.40 mysql_row_seek()
MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset) 

18.4.40.1 Description
    Ŀ(row cursor)  ġ Ѵ. ̸ ؼ   ü
 ü    ־ ϸ,  mysql_row_seek() Լ mysql_use_result() 
Լʹ    mysql_store_result() Լ   ִ.

offset μ mysql_row_tell() Լ mysql_row_seek() Լ ȣ ϰ Ǿ ϸ, 
  ܼ  ȣ ǹϴ  ƴϴ.   ȣ ̿Ͽ    
 ã ϴ 쿡 mysql_data_seek() Լ Ͽ Ѵ.

18.4.40.2 Return values
 Ŀ(row cursor) (previous value).   mysql_row_seek() Լ μ Ѱ
.

18.4.40.3 Errors
None. 


18.4.41 mysql_row_tell()
MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result) 

18.4.41.1 Description
 mysql_fetch_row() Լ ȣ⿡   Ŀ  ġ Ѵ. ̰ 
mysql_row_seek() Լ μ Ѱ.

mysql_row_tell() Լ ݵ mysql_store_result() Լ ڿ Ǿ ϸ, 
mysql_use_result() Լ ڿ   .

18.4.41.2 Return values
 Ŀ  (The current offset of the row cursor). 

18.4.41.3 Errors
None. 


18.4.42 mysql_select_db()
int mysql_select_db(MYSQL *mysql, const char *db) 

18.4.42.1 Description
db μ  ͺ̽ mysql μ  ῡ   ۾ ͺ̽
 ǵ Ѵ.  ǿ ؼ ⼭  ͺ̽ ͺ̽ 
  ̺   Ʈ ͺ̽ ȴ.

Ǿ ִ ڰ db μ  ͺ̽    ִٴ 
   mysql_select_db() Լ Ѵ.

18.4.42.2 Return values
̸ 0, ̸ 0 ƴ 

18.4.42.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.43 mysql_shutdown()
int mysql_shutdown(MYSQL *mysql) 

18.4.43.1 Description
ͺ̽  Ͽ ˴ٿǵ ûϸ,   ڰ ˴ٿ  
 ־ Ѵ. 

18.4.43.2 Return values
̸ 0, ̸ 0 ƴ  Ѵ.

18.4.43.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.44 mysql_stat()
char *mysql_stat(MYSQL *mysql) 

18.4.44.1 Description
'mysqladmin status' ɾ  Ǵ    ϰ ִ ڿ 
Ѵ.  ڿ ð(uptime), ǰ ִ  , (question), õ
(reload), ִ ̺    ԵǾ ִ.

18.4.44.2 Return values
 ¸ ϴ ڿ. ̸ NULL Ѵ.

18.4.44.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.45 mysql_store_result()
MYSQL_RES *mysql_store_result(MYSQL *mysql) 

18.4.45.1 Description
͸  (SELECT, SHOW, DESCRIBE, EXPLAIN) ࿡ Ͽ 
mysql_store_result() Ȥ mysql_use_result() Լ ȣѴ. 

mysql_store_result() Լ ü   Ŭ̾Ʈ о, MYSQL_RES ü 
Ҵϰ   ü ־ش. ƹ ൵ ϵ    (empty result 
set) ϵȴ(   NULL   ٸ).

mysql_store_result() Լ ȣϸ mysql_num_rows() Լ ȣϿ    
   ִ° ˾   ִ.
mysql_fetch_row() Լ ȣϿ  κ    , Ȥ 
mysql_row_seek() mysql_row_tell() Լ Ͽ     ġ ų 
ġ   ִ.

  ۾  ݵ mysql_free_result() Լ ȣش.

18.4.45.2 Return values
  MYSQL_RES  ü(result structure). ̸ NULL .

18.4.45.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_OUT_OF_MEMORY 
	Out of memory. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 

18.4.46 mysql_thread_id()
unsigned long mysql_thread_id(MYSQL *mysql) 

18.4.46.1 Description
 ῡ   ID Ѵ.   mysql_kill() Լ μ Ǿ 
带 kill ϴµ   ִ.

18.4.46.2 Return values
 ῡ   ID.

18.4.46.3 Errors
None. 

18.4.47 mysql_use_result()
MYSQL_RES *mysql_use_result(MYSQL *mysql) 

18.4.47.1 Description
͸  (SELECT, SHOW, DESCRIBE, EXPLAIN) ࿡ Ͽ 
mysql_store_result() Ȥ mysql_use_result() Լ ȣѴ. 

mysql_use_result() Լ    ۾ ʱȭ mysql_store_result() Լ
ó    о Ŭ̾Ʈ Ѱ ʴ´.  mysql_fetch_row() 
Լ  ȣν   ϳ . ̰   ӽ ̺
  ۿ  ʰ κ  о Ǹ, ̴ mysql_store_result() 
 Ͽ   ξ   ޸𸮸 Ѵ. Ŭ̾Ʈ  ࿡  
޸𸮿 max_allowed_packet Ʈ   ִ  (communication buffer)
 Ҵϰ ȴ.

 Ŭ̾Ʈ   ະ  ۾ ϰ ְų ڰ ^S(stop scroll)
 Է  ִ ũ   쿡 mysql_use_result() Լ 
ؼ ȵȴ. ̰  ߰ ϰ ٸ  Ͽ Ͱ  
 ̺ ϴ   ȴ.

mysql_use_result() Լ   NULL  ϵ  mysql_fetch_row() Լ 
ؾ Ѵ. ׷       ǿ    
Ѿ ϵǰ ȴ. C API 'Commands out of sync'  ְ ȴ. 

mysql_use_result() Լκ ϵǴ  ؼ mysql_data_seek(), mysql_row_seek(), 
mysql_row_tell(), mysql_num_rows(), mysql_affected_rows()  Լ   , 
Ȥ mysql_use_result() Լ   ٸ Ǹ   (׷  
 fetch Ŀ mysql_num_rows() Լ  fetchǾ   Ѵ).

 ¿  ۾  ݵ mysql_free_result() ȣϿ Ѵ.

18.4.47.2 Return values
MYSQL_RES  ü(result structure). ̸ NULL Ѵ.

18.4.47.3 Errors
CR_COMMANDS_OUT_OF_SYNC 
	Commands were executed in an improper order. 
CR_OUT_OF_MEMORY 
	Out of memory. 
CR_SERVER_GONE_ERROR 
	The MySQL server has gone away. 
CR_SERVER_LOST 
	The connection to the server was lost during the query. 
CR_UNKNOWN_ERROR 
	An unknown error occurred. 


18.4.48 mysql_query() Լ Ͽµ mysql_store_result() Լ  NULL 
  ΰ?

̷ 찡    ̰   ϳ Ȳ ߻Ͽ ǹѴ:

malloc()  (    ʹ ū ).
͸    (ῡ   ).
ǰ ƹ ͵  ʴ (INSERT, UPDATE, DELETE   ).

ǹ  (non-empty)  Ͽ mysql_num_fields() Լ ȣ
ν üũغ  ִ.  mysql_num_fields() Լ 0 ϸ   
   ǰ INSERT, DELETE    ʴ ǹ ǹѴ. 
mysql_num_fields() Լ 0 ƴ  ϴ  ǹ  ʴ  
ؾ߸  ǹѴ. ڼ  mysql_num_fields() Լ  Ѵ.

mysql_errno() Լ mysql_error() Լ ȣν  ׽Ʈغ  ִ.

18.4.49 What results can I get from a query?

  ϵǴ  ¿ ٿ       ִ:
- mysql_affected_rows() INSERT, UPDATE, DELETE     ֱٿ  
ǿ  affected   Ѵ. ܴ DELETE WHERE  
Ǿ ̺ truncateǴ ̴.   mysql_affected_rows() Լ affected
 ڵ ڷμ 0 Ѵ.
- mysql_num_rows()      Ѵ. mysql_store_result() Լ Բ 
Ǵ 쿡 mysql_store_result() ϵڸ ִ  mysql_num_rows() 
ȣǾ. mysql_use_result() Բ Ǵ 쿡 mysql_fetch_row()  
 ؼ  Ŀ mysql_num_rows() ȣ  ִ.
- mysql_insert_id() AUTO_INCREMENT ε  ̺  ߰   
ֱٿ   ID Ѵ.
-  ǵ(LOAD DATA INFILE ..., INSERT INTO ... SELECT ..., UPDATE) ΰ 
 Ѵ.   mysql_info() Ͽ ϵȴ. mysql_info()  ΰ
    NULL Ѵ.

18.4.50 How can I get the unique ID for the last inserted row?
AUTO_INCREMENT Ӽ  ÷ ϰ ִ ̺ ο ڵ带 
ϴ , mysql_insert_id() Լ ȣν  ֱٿ  ID   ִ.

 ڵ带 ν AUTO_INCREMENT ε Ǿ üũ   ִ. 
 ǰ AUTO_INCREMENT ε  INSERT ΰ üũ  ִ:

if (mysql_error(&mysql)[0] == 0 && mysql_num_fields(result) == 0 && mysql_insert_id(&mysql) != 0)
{
    used_id = mysql_insert_id(&mysql);
}

ϳ ̺  ID ٸ ̺ ϴµ ϰ ϴ   
 SQL  Ѵ:

INSERT INTO foo (auto, text) VALUES(NULL, 'text');             # generate ID by inserting NULL
INSERT INTO foo2 (id, text) VALUES(LAST_INSERT_ID(), 'text');  # use ID in second table


18.4.51 Problems linking with the C API
 ýۿ C API linkingϿ      ߻  ִ:

gcc -g -o client test.o -L/usr/local/lib/mysql -lmysqlclient -lsocket -lnsl

Undefined       first referenced
symbol          in file
floor            /usr/local/lib/mysql/libmysqlclient.a(password.o)
ld: fatal: Symbol referencing errors. No output written to client

   ߻ϴ 쿡  ÿ -lm ߰ν math ̺귯 
Բ  ش.


18.4.52 How to make a thread-safe client
Ŭ̾Ʈ  thread-safeϴ.  ū  'net.c'  д ƾ 
interrupt-safe ϴٴ ̴. ̰ κ д ۾  ɸ  Ŭ̾
Ʈ ۾ ߴ  ִ   ̶  ׷ ۼǾ.

ǥ Ŭ̾Ʈ ̺귯  ɼǾ ϵǾ.

Thread-safe Ŭ̾Ʈ  ؼ -lmysys, -lstring, -ldbug ̺귯  
ϴ net_serv.o Բ Ѵ.

Threaded client ϸ 'thr_alarm.c' Ͽ ִ ƾ Ȱ  ִ. mysys ̺
 ƾ   ؾ  Ѱ my_init() Լ   ȣؾ 
Ѵٴ ̴!

mysql_real_connect()   Լ  thread-safeϴ.  뿡 thread-
safe Ŭ̾Ʈ ̺귯 ϴ  װ thread-safe  ϴ 
 Ͽ Ѵ(⼭ mysql_real_connect() Լ   ϸ, 
mysql_connect() 쿡 ϴ).

mysql_real_connect() Լ thread-safeϰ  ؼ   Ŭ̾Ʈ 
Ͽ Ѵ:

shell> CPPFLAGS=-DTHREAD_SAFE_CLIENT ./configure ...

pthread ̺귯 Ʈ ԵǾ ʱ  ǥ Ŭ̾Ʈ(standard client) 
ŷ  ǵ  ɺ(undefined symbols)   ߻  ִ.

  'libmysqld.a' ̺귯  thread-safeϴ. ̰ ΰ thread 
mysql_real_connect()  ϵ   ڵ(connection handle) ÿ  
ʴ  Ŭ̾Ʈ ڵ尡 thread-safeϴٴ  ǹѴ; Ŭ̾Ʈ/  
־ ῡ Ͽ ÿ ϳ û Ѵ.  ῡ Ͽ  
thread ϱ ϴ 쿡 mysql_query() mysql_store_result() ȣ (call 
combination) Ͽ mutex lock ؾ Ѵ.

ϴ mysql_store_result() غǸ lock    ٸ thread  
 Ͽ query   ִ(ٽ ؼ,  ٸ thread  locking 
 ϴ  mysql_store_result() Բ   ٸ MYSQL_RES ͵ 
  ִ). POSIX thread α׷Ѵٸ mutex lock ϰ ϴµ 
pthread_mutex_lock() pthread_mutex_unlock() Լ   ִ.

mysql_store_result() ƴ mysql_use_result() ϴ 쿡 lock mysql_use_result()
 mysql_fetch_row()  ȣ surround ʿ䰡 ִ. ׷ threaded client 
mysql_use_result()  ʴ   .

