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 <=¥³¥á¥ó¥È¥¢¥¦¥È¤¹¤ë ¡¡ ¥í¥°¥Õ¥¡¥¤¥ë¤Î¼ïÎà
¥Æ¡¼¥Ö¥ë̾¤ÎÊѹ¹¡¦Ê£À½ †¥Æ¡¼¥Ö¥ë̾¤ÎÊѹ¹ 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) |