'MySQL' 태그의 글 목록

mysql, db fetch perl api sample

카테고리 없음 2014. 3. 18. 16:42

#!/usr/bin/perl

#use Mysql;

use DBI;

use DBD::mysql;

# CONFIG VARIABLES

$platform = "mysql";

$database = "MYDATABASE";

$host = "localhost";

$port = "3306";

$user = "MYUSER";

$pw = "MYPASSWORD";

# DATA SOURCE NAME

$dsn = "dbi:mysql:$database:$host:$port";

# PERL DBI CONNECT

$connect = DBI->connect($dsn, $user, $pw);

fetch_db("MYTABLE");

sub fetch_db() {

my($table) = @_;

my $file = $table . ".txt";

open($FH, ">$file") or die("Can't open : $file $!\n");

print "테이블 $table\n";

print $FH "테이블 $table\n";

get_count($table);

print $FH "\n";

print "\n";

print $FH "---END--\n";

close $FH;

}

sub get_count() {

my($table) = @_;

# PREPARE THE QUERY

$query = "select TYPE, CAST(STORE_ID AS UNSIGNED INTEGER), count(*) from $table group by TYPE, CAST(STORE_ID AS UNSIGNED INTEGER)";

$query_handle = $connect->prepare($query);

# EXECUTE THE QUERY

$query_handle->execute();

# BIND TABLE COLUMNS TO VARIABLES

$query_handle->bind_columns(undef, \$id, \$type, \$cnt);

print "* 샵 별 건수\n";

print $FH "* 샵 별 건수\n";

# LOOP THROUGH RESULTS

print "TYPE\tSHOP_ID\tCNT\n";

print $FH "TYPE\tSHOP_ID\tCNT\n";

print "-------\t----\t---\n";

print $FH "-------\t----\t---\n";

while($query_handle->fetch()) {

print "$id\t$type\t$cnt\n";

print $FH "$id\t$type\t$cnt\n";

}

트랙백 : 댓글

mysql, 서버 접속 정보 status

mysql 2014. 3. 6. 14:16

출처:http://stackoverflow.com/questions/3613704/mysql-show-current-connection-info

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.5.8, for Win32 (x86)

Connection id:          1
Current database:       test
Current user:           ODBC@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.5.8 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 7 min 16 sec

Threads: 1  Questions: 21  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 0.48
--------------

mysql>

트랙백 : 댓글

mysql 문자 집합 설정

mysql 2014. 3. 2. 00:06

etc/my.cnf 에 등록하면 된다

참조:http://stackoverflow.com/questions/14432299/should-etc-my-cnf-be-set-to-add-character-set-server-utf8-and-default-character

/etc/my.cnf.

[mysqld]

default-character-set=utf8   
character-set-server=utf8  
skip-character-set-client-handshake  
collation-server=utf8_unicode_ci  
init-connect='SET NAMES utf8'  
init_connect='SET collation_connection = utf8_general_ci'  
port = 3306

트랙백 : 댓글

mysql, INSERT ... ON DUPLICATE KEY UPDATE

mysql 2014. 2. 27. 16:30

츨처:https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

mysql> create table test3 (a int primary key, b int ,c int);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test3 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

Query OK, 1 row affected (0.01 sec)

mysql> select * from test3;

+---+------+------+

| a | b | c |

+---+------+------+

| 1 | 2 | 3 |

+---+------+------+

1 row in set (0.00 sec)

mysql> INSERT INTO test3 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

Query OK, 2 rows affected (0.00 sec)

mysql> select * from test3;

+---+------+------+

| a | b | c |

+---+------+------+

| 1 | 2 | 4 |

+---+------+------+

1 row in set (0.00 sec)

mysql> INSERT INTO test3 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

Query OK, 2 rows affected (0.00 sec)

mysql> select * from test3;

+---+------+------+

| a | b | c |

+---+------+------+

| 1 | 2 | 5 |

+---+------+------+

1 row in set (0.00 sec)

트랙백 : 댓글

mysql 테이블 크기

mysql 2014. 2. 27. 11:08

출처:http://blog.mongee.net/7

select table_name,(data_length)/(1024*1024) as 'DATA(MB)',(index_length)/(1024*1024) as 'INDEX(MB)' from information_schema.tables where table_name like '%_TBL';