꼬장

SELECT * FROM 테이블 WHERE CHAR_LENGTH(컬럼) > 갯수;
SELECT * FROM 테이블 WHERE LENGTH(컬럼) > 갯수;

Centos7 

vi /etc/my.cnf

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
max_connections = 1500
wait_timeout = 60
max_allowed_packet = 512M



[client]
port=3306
default-character-set = utf8

[mysqldump]
default-character-set = utf8

[mysql]
default-character-set = utf8

확인

show variables like 'c%';

●설치전 파일 생성

# vi /etc/yum.repos.d/MariaDB.repo

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.1/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

●설치

# yum install MariaDB-server

●확인

# rpm -qa | grep MariaDB

●부팅시 자동실행

systemctl enable MariaDB

● general log 상태 확인 및 설정

1) mysql> show variables like 'general%'

2) mysql> show variables where Variable_name in ('version','log','general_log');

+------------------+-------------------------------+
| Variable_name    | Value                            |
+------------------+-------------------------------+
| general_log      | OFF                                |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+-------------------------------+

+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| general_log   | OFF          |
| log              | OFF          |
| version         | 5.1.73-log  |
+---------------+------------+

general_log : 로그 기록유무

general_log_file : 로그 저장될 위치

mysql> set  global general_log = on; ( 활성화 )

● general log 쌓는 방식 확인 및 설정

mysql> show variables like 'log_output'; ( 확인 )

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE    |
+---------------+-------+

mysql> set global log_output='방식' ( FILE 혹은 TABLE )

테이블 방식 쌓기일때 비우기 명령

mysql> truncate mysql.general_log;

초기화

alter table 테이블명 auto_increment = 1

재정렬

SET @COUNT = 0;
UPDATE `테이블명` SET `테이블명`.`컬럼명` = @COUNT:=@COUNT+1;

ALTER TABLE `테이블명` AUTO_INCREMENT=1(설정할 인덱스번호);

#날짜 변환

DATE_FORMAT(now(),'%Y%m%d%H%i%s')

#범주조회

SELECT DISTINCT

※ COUNT 가능

출처 : http://blog.naver.com/roland82/120205080301

vi /etc/my.cnf

[client]
default-character-set=utf8

[mysqld]
character-set-client-handshake=FALSE
init_connect="SET collation_connection = utf8_general_ci"
init_connect="SET NAMES utf8"
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci

[mysqldump]
default-character-set=utf8

[mysql]
default-character-set=utf8

● 추가

alter table [테이블명] add [컬럼명][타입] default [값] [위치];

예) mysql> alter table T_TABLE add T_ADDCOLUMN varchar(255) not null default '' after T_EXISTCOLUMN;

● 삭제

alter table [테이블명] drop [컬럼명]
예) mysql> alter table T_TABLE drop T_COLUMNNAME;

● 변경

alter table [테이블명] change [기존컬럼명] [새컬럼명] [새컬럼타입][default옵션값]
예) mysql> alter table T_TABLE change T_EXISTCOLUMN T_NEWCOLUMNS varchar(255) NOT NULL DEFAULT '';

● 타입 변경

alter table [테이블명] modify [컬럼명] [변경할타입]
예) mysql> alter table T_TABLE modify T_EXISTCOLUMN varchar(50) ;

● 디폴트 변경

alter table [테이블명] alter column [컬럼명] set default '';

예) alter table T_TABLE  alter column T_EXISTCOLUMN set default '';

CONVERT(VARCHAR(20), GETDATE(), 0) /*07 15 2010 1:54PM */
CONVERT(VARCHAR(20), GETDATE(), 1) /*07/15/15 */
CONVERT(VARCHAR(20), GETDATE(), 2) /*10.07.15 */
CONVERT(VARCHAR(20), GETDATE(), 3) /*15/07/15 */
CONVERT(VARCHAR(20), GETDATE(), 4) /*15.07.10 */
CONVERT(VARCHAR(20), GETDATE(), 5) /*15-07-10 */
CONVERT(VARCHAR(20), GETDATE(), 6) /*15 07 10 */
CONVERT(VARCHAR(20), GETDATE(), 7) /*07 15, 10 */
CONVERT(VARCHAR(20), GETDATE(), 8) /*13:54:20 */
CONVERT(VARCHAR(20), GETDATE(), 9) /*07 15 2010 1:54:20: */
CONVERT(VARCHAR(20), GETDATE(), 10) /*07-15-10 */
CONVERT(VARCHAR(20), GETDATE(), 11) /*10/07/15 */
CONVERT(VARCHAR(20), GETDATE(), 12) /*100715 */
CONVERT(VARCHAR(20), GETDATE(), 13) /*15 07 2010 13:54:20: */
CONVERT(VARCHAR(20), GETDATE(), 14) /*13:54:20:790 */
CONVERT(VARCHAR(20), GETDATE(), 20) /*2010-07-15 13:54:20 */
CONVERT(VARCHAR(20), GETDATE(), 21) /*2010-07-15 13:54:20. */
CONVERT(VARCHAR(20), GETDATE(), 22) /*07/15/15 1:54:20 PM */
CONVERT(VARCHAR(20), GETDATE(), 23) /*2010-07-15 */
CONVERT(VARCHAR(20), GETDATE(), 24) /*13:54:20 */
CONVERT(VARCHAR(20), GETDATE(), 25) /*2010-07-15 13:54:20. */
CONVERT(VARCHAR(20), GETDATE(), 100) /*07 15 2010 1:54PM */
CONVERT(VARCHAR(20), GETDATE(), 101) /*07/15/2010 */
CONVERT(VARCHAR(20), GETDATE(), 102) /*2010.07.15 */
CONVERT(VARCHAR(20), GETDATE(), 107) /*15/07/2010 */
CONVERT(VARCHAR(20), GETDATE(), 104) /*15.07.2010 */
CONVERT(VARCHAR(20), GETDATE(), 105) /*15-07-2010 */
CONVERT(VARCHAR(20), GETDATE(), 106) /*15 07 2010 */
CONVERT(VARCHAR(20), GETDATE(), 107) /*07 15, 2010 */
CONVERT(VARCHAR(20), GETDATE(), 108) /*13:54:20 */
CONVERT(VARCHAR(20), GETDATE(), 109) /*07 15 2010 1:54:20: */
CONVERT(VARCHAR(20), GETDATE(), 110) /*07-15-2010 */
CONVERT(VARCHAR(20), GETDATE(), 111) /*2010/07/15 */
CONVERT(VARCHAR(20), GETDATE(), 112) /*20100715 */
CONVERT(VARCHAR(20), GETDATE(), 113) /*15 07 2010 13:54:20: */
CONVERT(VARCHAR(20), GETDATE(), 114) /*13:54:20:807 */
CONVERT(VARCHAR(20), GETDATE(), 120) /*2010-07-15 13:54:20 */
CONVERT(VARCHAR(20), GETDATE(), 121) /*2010-07-15 13:54:20. */
CONVERT(VARCHAR(20), GETDATE(), 126) /*2010-07-15T13:54:20. */
CONVERT(VARCHAR(20), GETDATE(), 127) /*2010-07-15T13:54:20. */
CONVERT(VARCHAR(20), GETDATE(), 130) /* 2 ???? ?????? 1431 */
CONVERT(VARCHAR(20), GETDATE(), 131) /* 2/04/1431 1:54:20: */