MySQLʸˡ

2009-06-09 () 08:48:10

log

log/var/lib/mysqlmysql-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_uriageIDեɤ

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-12345H)        | 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ǤϡƤͤ¿ͤ褦ȤȡϰϤޤ롣

data4Byte

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)

data5byteϤƤߤ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) X255ޤʸByteǤʤ

etcvarchar(4)˻ꤷƱ5byte4ʸ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'abc'(5Byte,4ʸ)

mysql> UPDATE t_test SET etc = 'abc' 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  |  | abc  |
+----+------------+--------+
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 (3298d)