이응기역

select -- *
COLUMN_NAME, COLUMN_COMMENT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMN_KEY, 
CASE
WHEN IS_NULLABLE = 'YES'
THEN 'N'
ELSE 'Y'
END as NOTNULL
, COLUMN_DEFAULT
from information_schema.columns
where table_schema = 'mytableschema'
and table_name = 'MY_TABLE'
order by ordinal_position
;

$ echo "SELECT now()" | mysql.exe -uuserid1234 -ppwd1234\!@ -h 127.0.0.1 -P 3306 mydb
now()
2021-02-09 14:53:14

(stackoverflow.com/questions/6150675/how-to-feed-mysql-queries-from-bash)

출처: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>

* db 파일 물리적 위치

mysql> show variables like 'datadir';

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

[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

츨처: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)

출처: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';

출처:

http://www.egovframe.go.kr/wiki/doku.php?id=egovframework:rte:psl:transaction#configuration

http://www.egovframe.go.kr/wiki/doku.php?id=egovframework:rte:psl:transaction:declarative_transaction_management

*  xml 정의

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
   <property name="dataSource" ref="dataSource"/>
</bean>
 
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
   <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
   <property name="url" value="dbc:mysql://db2:1621/rte"/>
   <property name="username" value="rte"/>
   <property name="password" value="xxx"/>
   <property name="defaultAutoCommit" value="false"/>
</bean>

(datasource.xml)

* 사용

@Transactional

public class Foo {

insert();

delete();

load_data();

}

CREATE TABLE `test2` (

  `col1` int(11) DEFAULT NULL,

  `col2` int(11) DEFAULT NULL,

  `ins_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

또는 

CREATE TABLE `test2` (

  `col1` int(11) DEFAULT NULL,

  `col2` int(11) DEFAULT NULL,

  `ins_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1045 MySQL 서버에 로그인할 수 없습니다

wamp 설치 후 phpmyadmin 에 로그인할 때 나는 에러이다

사용자 명에 "root" , 비밀번호 는 비워둔 채 엔터하면 된다