MySQLʸˡ

¹¹¿·Æü2009-06-09 (²Ð) 08:48:10

¥¢¥¯¥»¥¹log¾ðÊó

log¤Ï/var/lib/mysql¤Îmysql-bin.xxxx¤Ë¤¢¤ë¤¬¥Ð¥¤¥Ê¥ê¤Ê¤¿¤á¥Ä¡¼¥ë¤ò»ÈÍѤ¹¤ë

# mysqlbinlog mysql-bin.000002
#080124 18:05:01 server id 2  end_log_pos 127   Query   thread_id=11
exec_time=0     error_code=0
SET TIMESTAMP=1201165501;
INSERT INTO `foo` VALUES (1,'Okada'),(2,'Okumura'),(3,'Yamada');

¤Î¤è¤¦¤Ê¥í¥°¤¬¸«¤¨¤¤¤ë

mysqlbinlog: unknown variable 'default-character-set=utf8' ¤Î¤è¤¦¤Ê¥¨¥é¡¼¤¬½Ð¤¿¤émy.cnf¤Î

[client]
¡ôdefault-character-set = utf8 <=¥³¥á¥ó¥È¥¢¥¦¥È¤¹¤ë

¡¡ ¥í¥°¥Õ¥¡¥¤¥ë¤Î¼ïÎà

¥í¥°¤Î¼ïÎ४¥×¥·¥ç¥ó¥Ç¥Õ¥©¥ë¥È¤Î¥Õ¥¡¥¤¥ë̾ÀßÄê¥Õ¥¡¥¤¥ë¤Î½ÐÎÏ¥Õ¥¡¥¤¥ë̾»ØÄêÆâÍÆ
¥¨¥é¡¼¥í¥°--log-error¥Û¥¹¥È̾.errlog-error=¥Õ¥¡¥¤¥ë̾MySQL¥µ¡¼¥Ð¡¼µ¯Æ°»þ¡¢²Ôư»þ¡¢Ää»ß»þ¤Î¥¨¥é¡¼¤ä¥á¥Ã¥»¡¼¥¸
°ìÈÌ¥¯¥¨¥ê¡¼¥í¥°--log¥Û¥¹¥È̾.loglog=¥Õ¥¡¥¤¥ë̾Àܳ¤ä¼Â¹Ô¥¯¥¨¥ê¡¼¤Ë¤Ä¤¤¤Æ¤Î¾ðÊó
¥¹¥í¡¼¥¯¥¨¥ê¡¼¥í¥°--log-slow-queries¥Û¥¹¥È̾-slow.loglog-slow-queries=¥Õ¥¡¥¤¥ë̾long_query_time¤Ç»ØÄê¤ÎÉÿô¤òͤ¨¤ë¥¯¥¨¥ê¡¼
¥Ð¥¤¥Ê¥ê¥í¥°--log-bin¥Û¥¹¥È̾-bin.¿ôÃÍlog-bin=¥Õ¥¡¥¤¥ë̾¹¹¿·¤òȼ¤¦¥¯¥¨¥ê¡¼¤¬µ­Ï¿¤µ¤ì¡¢¥Ð¥Ã¥¯¥¢¥Ã¥×¤ä¥ì¥×¥ê¥±¡¼¥·¥ç¥ó¤Ë¤·¤è¤¦¤µ¤ì¤ë
InnoDB¤Î¥í¥°¥Ç¥Õ¥©¥ë¥È¤ÇÀ¸À®ib_logfile¿ôÃÍ-InnoDB¤Î¥í¥°¥Õ¥¡¥¤¥ë

¥Æ¡¼¥Ö¥ë̾¤ÎÊѹ¹¡¦Ê£À½

¥Æ¡¼¥Ö¥ë̾¤ÎÊѹ¹

ALTER TABLE ¸Å¤¤¥Æ¡¼¥Ö¥ë̾ RENAME ¿·¤·¤¤¥Æ¡¼¥Ö¥ë̾;

mysql> ALTER TABLE ism_test RENAME ism_test2;
Query OK, 0 rows affected (0.00 sec)

¥Æ¡¼¥Ö¥ë¤ÎÊ£À½

create table ¿·¤·¤¤¥Æ¡¼¥Ö¥ë̾ as select * from ¸µ¤Î¥Æ¡¼¥Ö¥ë̾;

mysql> create table ism_test as select * from ism_test2;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

ɬÍפʥ쥳¡¼¥É¤À¤±»Ä¤·¥³¥Ô¡¼¤¹¤ë¤³¤È¤â²Ä

create table ¿·¤·¤¤¥Æ¡¼¥Ö¥ë̾ as select * from ¸µ¤Î¥Æ¡¼¥Ö¥ë̾ where (¾ò·ï);

Selectʸ¤¤¤í¤¤¤í

½ÅÊ£¥­¡¼¤ò°ìÅÙ¤À¤±¼è¤ê½Ð¤¹ÊýË¡

distinct key1¤Î¤è¤¦¤Ë»ØÄꤹ¤ë¤Èkey1¤¬½ÅÊ£¤·¤Æ¤¤¤ë¤È¤­¤Ï1ÅÙ¤À¤±ÁªÂò¤µ¤ì¤ë ¤³¤Î¤è¤¦¤Ëdistinct¤ò»ØÄꤹ¤ë

mysql> select distinct ktaiid from t_gps;

¥ì¥³¡¼¥É¤ò»ØÄꤷ¤Æ¼èÆÀ

¤Ï¤¸¤á¤Î¥ì¥³¡¼¥É¤«¤é3¥ì¥³¡¼¥É¼èÆÀ

mysql> select * from ism_uriage limit 0,3;

3¥ì¥³¡¼¥ÉÌܤ«¤é4¥ì¥³¡¼¥É¼èÆÀ

mysql> select * from ism_uriage limit 2,4;

Æþ¤ì»Òɽ¸½

Æþ¤ì»Ò¤Ï()¤Ç°Ï¤à

¥Æ¡¼¥Ö¥ëism_shouhin¤Î¤¹¤Ù¤Æ¤ÎID¥Õ¥£¡¼¥ë¥É¤ÎÃͤÈÅù¤·¤¤¡¢¥Æ¡¼¥Ö¥ëism_uriage¤ÎID¥Õ¥£¡¼¥ë¥É¤òÃê½Ð

mysql> select * from ism_uriage where ID in (select ID from ism_shouhin);
+----+---------------------------+------+-----------+-----------+------+
| ID | date                      | uid  | shouhinid | uriagesuu | etc  |
+----+---------------------------+------+-----------+-----------+------+
|  1 | 2008/09/27 (Sat) 23:41:05 | 3    |        14 |         2 | NULL |
|  2 | 2008/09/27 (Sat) 23:41:05 | 3    |        16 |         4 | NULL |
|  3 | 2008/09/27 (Sat) 23:41:05 | 3    |        18 |         6 | NULL |
|  5 | 2008/09/27 (Sat) 23:45:02 | 3    |        11 |         1 | NULL |

(ά) 

| 33 | 2008/09/28 (Sun) 01:48:04 | 2    |        16 |         4 | NULL |
| 34 | 2008/09/28 (Sun) 01:50:02 | 2    |        20 |         2 | NULL |
| 35 | 2008/09/28 (Sun) 01:50:35 | 2    |        23 |         2 | NULL |
+----+---------------------------+------+-----------+-----------+------+
32 rows in set (0.01 sec)

¥Õ¥£¡¼¥ë¥É¤Î¹ç·×¤òÊÌ̾¤Çɽ¼¨

mysql> select uid, uriagesuu, uid+uriagesuu as sum from ism_uriage where shouhinid=9;
+------+-----------+------+
| uid  | uriagesuu | sum  |
+------+-----------+------+
| 3    |         2 |    5 |
| 2    |         2 |    4 |
| 2    |         4 |    6 |

¥Õ¥£¡¼¥ë¥É¤Îʸ»ú¤ÎÏ¢·ë

mysql> select uid, uriagesuu, concat(uid, uriagesuu) as gousei from ism_uriage where shouhinid=9;
+------+-----------+--------+
| uid  | uriagesuu | gousei |
+------+-----------+--------+
| 3    |         2 | 32     |
| 2    |         2 | 22     |
| 2    |         4 | 24     |

¿ôÃÍÈæ³Ó¤·¤Æ·ë²Ì¤òɽ¼¨

mysql> select uid, uriagesuu, strcmp(uid, uriagesuu) as hikaku from ism_uriage where shouhinid=9;
+------+-----------+--------+
| uid  | uriagesuu | hikaku |
+------+-----------+--------+
| 3    |         2 |      1 |
| 2    |         2 |      0 |
| 2    |         4 |     -1 |
| 2    |         4 |     -1 |
| 2    |         4 |     -1 |

select in

mysql> select * from ism_shouhin where ID in (10,12,15);
+------+---------------+------+--------+------+----------+------+
| ID   | name          | grp  | kakaku | url  | status   | etc  |
+------+---------------+------+--------+------+----------+------+
|   10 | ¥Ó¡¼¥ë 500ml  | 201  |    360 |      |          |      |
|   12 | ȯˢ¼ò 500ml  | 201  |    164 |      |          |      |
|   15 | ¥ï¥¤¥ó (¥í¥¼) | 201  |    680 |      | ÈÎÇ佪λ |      |
+------+---------------+------+--------+------+----------+------+
3 rows in set (0.00 sec)

ÈÝÄê·Á [not in]

mysql> select * from ism_shouhin where ID not in (10,12,15);
+------+-------------------+------+--------+-----------------+------
| ID   | name              | grp  | kakaku | url | status    | etc  |
+------+-------------------+------+--------+-----+-----------+------
|    9 | ¥Ó¡¼¥ë 350ml      | 201  |    250 |     |           |      |
|   11 | ȯˢ¼ò 350ml      | 201  |    134 |     |           |      |
|   13 | ¾ÆÃñ (°ò)         | 201  |   1048 |     |           |      |
|   14 | ¾ÆÃñ (Çþ)         | 201  |   1148 |     |           |      |
|   16 | ¥ï¥¤¥ó (Çò)       | 201  |    650 |     |           |      |
+------+-------------------+------+--------+-----+-----------+-------

¥Æ¡¼¥Ö¥ë̾¤Î°ì»þÊѹ¹

mysql> select ID,name as ̾Á°,kakaku as ñ²Á from ism_shouhin where ID in(10,12,15);
+------+---------------+------+
| ID   | ̾Á°          | ñ²Á |
+------+---------------+------+
|   10 | ¥Ó¡¼¥ë 500ml  |  360 |
|   12 | ȯˢ¼ò 500ml  |  164 |
|   15 | ¥ï¥¤¥ó (¥í¥¼) |  680 |
+------+---------------+------+
3 rows in set (0.00 sec)

¥Æ¡¼¥Ö¥ë·ë¹ç

select ism_uriage.* , ism_shouhin.* from ism_uriage join ism_shouhin on  
ism_uriage.shouhinid=ism_shouhin.ID where ism_uriage.uid=2 order  by
ism_uriage.ID desc limit 3;
+-----+---------------------------+------+-----------+-----------+------+------+
----------------------------+------+--------+-----------------------------------
--------------------------------------------+--------+------+
| ID  | date                      | uid  | shouhinid | uriagesuu | etc  | ID    |
 name                       | grp  | kakaku | url    | status | etc  |
+-----+---------------------------+------+-----------+-----------+------+------+
----------------------------+------+--------+-----------------------------------
--------------------------------------------+--------+------+
| 160 | 2008/10/02 (Thu) 15:52:20 | 2    |         1 |         1 | NULL |    1 |
 Îä¢¸Ë AB-12345¡ÊH)        | 101  |  45000 |        |        |      |
| 159 | 2008/10/02 (Thu) 15:52:20 | 2    |        18 |         4 | NULL |   18 |
 ¥«¥¯¥Æ¥ë¥Ð¡¼(¥ì¥â¥ó)       | 201  |    121 |        |        |      |
| 158 | 2008/10/02 (Thu) 15:52:20 | 2    |        21 |         2 | NULL |   21 |
 ¥³¥ó¥Ñ¥¯¥È¥«¥á¥é(Leica C2) | 301  |  37590 |        |        |      |
+-----+---------------------------+------+-----------+-----------+------+------+
----------------------------+------+--------+-----------------------------------
--------------------------------------------+--------+------+

3 rows in set (0.01 sec)

¥Æ¡¼¥Ö¥ëºîÀ®¡¦ºï½ü»þ¤Î¥Æ¡¼¥Ö¥ë¸ºß¤Î³Îǧ

mysql> DROP TABLE IF EXISTS ism_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table if not exists ism_test( ID int8, date  varchar(64), etc  varchar(64));
Query OK, 0 rows affected (0.02 sec)

¥Õ¥£¡¼¥ë¥É̾¤ª¤è¤Ó·¿¤ÎÊѹ¹

·¿¤ÎÊѹ¹(data)

mysql> show fields from ism_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | bigint(20)  | YES  |     | NULL    |       |
| date  | varchar(64) | YES  |     | NULL    |       |
| etc   | varchar(64) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> alter table ism_test change date date int8;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show fields from ism_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | bigint(20)  | YES  |     | NULL    |       |
| date  | bigint(20)  | YES  |     | NULL    |       |
| etc   | varchar(64) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

̾Á°¤ÎÊѹ¹(data)

mysql> alter table ism_test change date data varchar(64);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show fields from ism_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | bigint(20)  | YES  |     | NULL    |       |
| data  | varchar(64) | YES  |     | NULL    |       |
| etc   | varchar(64) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

½ñ¼°·Á¼°

alter table <¥Æ¡¼¥Ö¥ë̾> change <¥Õ¥£¡¼¥ë¥É̾> <¿·¥Õ¥£¡¼¥ë¥É̾>¡¡<·¿> 

ÆþÎϤµ¤ì¤ë¥Ç¡¼¥¿¤ÎÈϰϱۤ¨¤Î¤È¤­

MySQL¤Ç¤Ï¡¢ÄêµÁ¤µ¤Æ¤¤¤ëÃͤè¤ê¿¤¤ÃͤòÆþ¤ì¤è¤¦¤È¤¹¤ë¤È¡¢µöÍÆÈÏ°Ï¤Þ¤ÇÆþ¤ë¡£

data¤¬4Byte

mysql> show fields from ism_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int(11)     | YES  |     | NULL    |       |
| data  | varchar(4)  | YES  |     | NULL    |       |
| etc   | varchar(64) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

data¤Ë5byteÆþÎϤ·¤Æ¤ß¤ë¤È4Byte¤Þ¤ÇÆþ¤ë

mysql> insert into ism_test values(1,'12345','²¬ÅÄ');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from ism_test;
+------+------+------+
| ID   | data | etc  |
+------+------+------+
|    1 | 1234 | ²¬ÅÄ |
+------+------+------+
1 row in set (0.00 sec)

¥Õ¥£¡¼¥ë¥É¤Î·¿¤òÊѹ¹¤¹¤ë¤ÈÌ·½â¤¹¤ë¤ÈÃͤ¬¤Ê¤¯¤Ê¤ë ¡Êvarchar=>int·¿¤ËÊѹ¹¤·¤¿¾ì¹ç)

mysql> select * from ism_test;
+------+------+------+
| ID   | data | etc  |
+------+------+------+
|    1 | 1234 | ²¬ÅÄ |
+------+------+------+
1 row in set (0.00 sec)
mysql> alter table ism_test change etc etc int4;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1
mysql> select * from ism_test;
+------+------+------+
| ID   | data | etc  |
+------+------+------+
|    1 | 1234 |    0 |
+------+------+------+
1 row in set (0.00 sec)

¥Õ¥£¡¼¥ë¥É¤ÎÄɲᦺï½ü

ÄɲÃ

mysql> show fields from ism_test;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| ID    | int(11)    | YES  |     | NULL    |       |
| data  | bigint(20) | YES  |     | NULL    |       |
| etc   | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER table ism_test add etc2 varchar(64);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> show fields from ism_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int(11)     | YES  |     | NULL    |       |
| data  | bigint(20)  | YES  |     | NULL    |       |
| etc   | int(11)     | YES  |     | NULL    |       |
| etc2  | varchar(64) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

ºï½ü

mysql> ALTER table ism_test drop etc2;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

varchar(X)¤ÎX¤Ë¤Ä¤¤¤Æ

varchar(X) X¤Ï255¤Þ¤Çʸ»ú¿ô¤ò»ØÄêByte¿ô¤Ç¤Ê¤¤

etc¤òvarchar(4)¤Ë»ØÄꤷ¡¢Æ±¤¸5byte¤Ç4ʸ»ú¤È5ʸ»ú¤ÇÈæ³Ó

mysql> show fields from t_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| No    | text         | NO   |     |         |       |
| data  | varchar(128) | NO   |     |         |       |
| etc   | varchar(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

etc¤Ë'ab¤¢c'(5Byte,4ʸ»ú)¤òÆþ¤ì¤ë¤ÈÆþ¤ë

mysql> UPDATE t_test SET etc = 'ab¤¢c' WHERE No = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from t_test;
+----+------------+--------+
| No | data       | etc    |
+----+------------+--------+
| 1  | ABCD       | ¤¢¤¤¤¦ |
| 2  | ¤¢¤¤¤¦¤¨¤ª | ab¤¢c  |
+----+------------+--------+
2 rows in set (0.00 sec)

etc¤Ë'abcde'(5Byte,5ʸ»ú)¤òÆþ¤ì¤ë¤ÈÆþ¤é¤Ê¤¤

mysql> UPDATE t_test SET etc = 'abcde' WHERE No = 2;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
mysql> select * from t_test;
+----+------------+--------+
| No | data       | etc    |
+----+------------+--------+
| 1  | ABCD       | ¤¢¤¤¤¦ |
| 2  | ¤¢¤¤¤¦¤¨¤ª | abcd   |
+----+------------+--------+
2 rows in set (0.00 sec)

¥È¥Ã¥×   ÊÔ½¸ Åà·ë º¹Ê¬ ¥Ð¥Ã¥¯¥¢¥Ã¥× źÉÕ Ê£À½ ̾Á°Êѹ¹ ¥ê¥í¡¼¥É   ¿·µ¬ °ìÍ÷ ñ¸ì¸¡º÷ ºÇ½ª¹¹¿·   ¥Ø¥ë¥×   ºÇ½ª¹¹¿·¤ÎRSS
Last-modified: 2009-06-09 (²Ð) 08:48:10 (3541d)