MYSQL에서 POINT형 데이터를 이용해서 특정 위치로부터의 거리 구하기

테이블

CREATE TABLE tb_contents
(
tc_seq  INTEGER NOT NULL AUTO_INCREMENT COMMENT '콘텐츠일련번호',
tc_coordinate  POINT NULL COMMENT '콘텐츠좌표',
PRIMARY KEY (tc_seq),
) COMMENT='콘텐츠';

쿼리 예문 : 2가지 계산식

SELECT
ST_X(TC_COORDINATE) AS coordinatex
, ST_Y(TC_COORDINATE) AS coordinatey
, (
6371 *
ACOS(
COS(RADIANS(ST_Y(TC_COORDINATE))) *
COS(RADIANS(33.51165920146972)) *
COS(RADIANS(126.52084379894258) -
RADIANS(ST_X(TC_COORDINATE))) +
SIN(RADIANS(ST_Y(TC_COORDINATE))) *
SIN(RADIANS(33.51165920146972))
)
) AS distance1 /*거리측정 예1*/
, (SQRT(POW(X(TC_COORDINATE) - 126.52084379894258, 2) + POW(Y(TC_COORDINATE) - 33.51165920146972, 2)) * 111.2 /*Km*/) AS distance2 /*거리측정 예2*/
FROM TB_CONTENTS TC
ORDER BY distance1 ASC

두 가지 계산 결과는 조금 차이가 있었다.

MariaDB 설치, 세팅, database생성, 권한부여

1. MariaDB를 설치

mariadb-10.2.8-winx64.msi를 받아서 설치한다.

2. MariaDB에 로그인 및 계정 생성

시작 > 모든 프로그램 > MariaDB 10.1 > Command Prompt 오른클릭 > 자세히 > 관리자 권한으로 실행

root 유저로 접속
mysql -uroot -p[비번]

임의의 계정 생성
create user 'portal' identified by '비번';

윈도우에서와 리눅스에서는 계정 생성시 '@도메인'을 붙이고 안붙이고의 차이가 있는 모양임.
지금은 윈도우에서 계정 생성한 부분의 예임

3. DB의 캐릭터셋 조회 및 설정

쿼리로 캐릭터 셋을 조회하면 아래처럼 결과가 나왔다.

show variables like 'c%';

+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | euckr                                          |
| character_set_connection | euckr                                          |
| character_set_database   | utf8                                          |
| character_set_filesystem | binary                                        |
| character_set_results    | euckr                                          |
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          |
| character_sets_dir       | C:\Program Files\MariaDB 10.2\share\charsets\ |
| check_constraint_checks  | ON                                            |
| collation_connection     | euckr_korean_ci                               |
| collation_database       | utf8_general_ci                               |
| collation_server         | utf8_general_ci                               |
| completion_type          | NO_CHAIN                                      |
| concurrent_insert        | AUTO                                          |
| connect_timeout          | 10                                            |
+--------------------------+-----------------------------------------------+

utf8로 모두 변경하도록 한다.
C:\Program Files\MariaDB 10.2\data\my.ini 파일을 열어 아래 사항을 추가한다.

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

[mysqld]
init_connect="SET collation_connection = utf8_general_ci"
init-connect='SET NAMES utf8'
character-set-server=utf8
collation-server = utf8_general_ci

설정 변경 후에는 MariaDB 재 로그인 후 쿼리가 정상 작동되었다.
윈도우의 서비스를 열어 MariaDB(MySql)를 재구동 후 다시 캐릭터 셋을 조회하면 아래처럼 결과가 나왔다

+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | utf8                                          |
| character_set_connection | utf8                                          |
| character_set_database   | utf8                                          |
| character_set_filesystem | binary                                        |
| character_set_results    | utf8                                          |
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          |
| character_sets_dir       | C:\Program Files\MariaDB 10.2\share\charsets\ |
| check_constraint_checks  | ON                                            |
| collation_connection     | utf8_general_ci                               |
| collation_database       | utf8_general_ci                               |
| collation_server         | utf8_unicode_ci                               |
| completion_type          | NO_CHAIN                                      |
| concurrent_insert        | AUTO                                          |
| connect_timeout          | 10                                            |
+--------------------------+-----------------------------------------------+

4. 데이터베이스 생성

기본 생성문
create database egov_portal;

아래의 쿼리로 데이터베이스의 캐릭터셋을 확인할 수 있다.
select default_character_set_name from information_schema.schemata where schema_name = "egov_portal";

5. 유저에게 데이터베이스 권한 부여

grant all on eqov_portal.* to 'portal';

이때 스키마에 _(언더바)가 들어간 경우 관리 툴 인 HeidiSQL에서 스키마 이동이 잘 되지 않는 문제가 있었다.
그래서 HeidiSQL의 기능에서 직접 해 보았다.
상단 아이콘 중 [사용자 인증 및 권한관리] > 사용자 선택 후 [접근허용]에서 [객체추가] 해서 권한을 부여함.
이렇게 하니 [eqov_portal]가 [eqov\_portal]로 표시되며 잘 작동되었다.

6. 한글 insert 문제

Command Prompt에서 한글이 섞인 데이터를 insert시 아래와 같은 에러를 내며 데이터가 인서트 되지 않았다. DB와, Table 생성시 캐릭터 셋을 지정하면 문제가 없다는 내용을 여럿 접했는데 잘 되지 않았다.

ERROR 1366 (22007): Incorrect string value: '\xC0\xFC\xC0\xDA\xC1\xA4...' for column 'CL_CODE_NM' at row 1

이 문제는 MariaDB Command Prompt에서의 한글 문제로 생각되며 별도의 tool(HeidiSQL_9.4.0.5125_Setup.exe)을 사용하여 insert 시 정상적으로 처리할 수 있었다.

MY-SQL : 그룹지어서 카운트 세기

아래처럼 group by후 조회되는 항목의 count를 알아 내려고 했는데 잘 되지 않았다. 

SELECT 

COUNT(*) AS CNT 

FROM log_ymdh 

WHERE 1 = 1 

GROUP BY Y, M 

+-----+ 

| CNT | 

+-----+ 

| 100 | 

|  84 | 

|  63 | 

|  60 | 

|  61 | 

|  57 | 

| 110 | 

+-----+ 

7 rows in set (0.00 sec) 

원하는 결과 값은 쿼리 결과물 총 행수에 해당하는 7 이어야 한다. 

이 경우 group by 이전의 cnt 값이 나왔는데 

my-sql에서 서브쿼리가 되지 않아서 group by로는 더이상 이 문제를 해결할 수 없었다. 

아래는 distinct를 이용해서 성공한 쿼리문이다. 

SELECT 

COUNT(DISTINCT Y, M) AS CNT 

FROM log_ymdh 

WHERE 1 = 1 

+-----+ 

| CNT | 

+-----+ 

|  7 | 

+-----+ 

1 row in set (0.00 sec)

MY-SQL 테이블 변경

MY-SQL 테이블 변경

* 컬럼 추가하기
ALTER TABLE 테이블명 ADD 컬럼명 데이터타입;

* 테이블에서 컬럼 제거
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

* 테이블 이름 변경하는 쿼리문
RENAME  TABLE  이전테이블명  to  바꿀테이블명;

* 테이블 컬럼 명 수정하기
alter table 테이블명 change 이전컬럼명 바꿀컬럼명 데이터타입;

예문)
alter table bbs_cfg change bs_link_yn bs_list_type CHAR(1) NOT NULL;

MySQL 백업, 복원 패스워드 복원

MySQL bin폴더의 실행 명령들

* 패스워드 변경후
    root 패스워드 : user 테이블의 password를 null로 초기화 --> mysql을 shotdown --> mysql을 start --> mysqladmin -u root reload --> mysql -u root

* 데이터베이스 백업(일반)
    mysqldump -u user_id -p db_name > backup_file_name

* 데이터베이스 백업(한글이 깨질 시) - DB의 언어 설정이 한글이 아닐시 백업받으면 한글이 깨지는 문제 해결을 위한 처리
    mysqldump -u user_id -p db_name --default-character-set=latin1 > backup_file_name //DB의 언어 설정이 latin1 일때 옵션 사용방법

* 데이터베이스 복원
    mysql -u user_id -p db_name < backup_file_name

MY-SQL : 데이터베이스 Setting 순서

데이터베이스 Setting 순서(mysql-3.23.53-win.zip) =====================

1) mysql(mysql-3.23.53-win)을 설치한다.
2) 다음의 명령을 실행한다.
    C:\mysql\bin> winmysqladmin.exe
    (데몬시작  /etc/rc.d/init.d/mysqld start) ??????????????
3) user과 password를 만드는 화면이 나오면 유저와 패스워드를 정해준다.
4) 트레이에 있는 신호등 아이콘에서 파란색 불이 들어왔는지 확인한다.
5) mysql에 접속한다.
    C:\mysql\bin> mysql
6) 데이터베이스를 생성한다.
    mysql> create database 데이터베이스 명;
7) 접속하는 사용자를 등록한다. 여기서는 전체 데이터베이스에 대해 사용할 수 있는 명령의 제한을 설정할 수 있다.
    mysql> use mysql  -- mysql 데이터베이스를 사용하겠다고 선언함
    mysql> insert into user (host, user, password) values ('localhost', '유저명', password('패스워드'));
8) 각 db별로 접근 가능한 사용자를 등록한다. 여기에 지정된 유저만이 지정된 db에 접근 가능하다.
    mysql> insert into db values ('localhost', 'db명', '유저명', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y');
9) mysql를 빠져 나간다.
    mysql> exit
10) 저장된 사용자를 사용 가능하게 한다.
    C:\mysql\bin> mysqladmin -u root reload (비밀번호가 없을 때)
    C:\mysql\bin> mysqladmin -u root -pDBpassword reload (비밀번호가 있을 때에는 -p를 비밀번호와 띄우지 않는다)
11) DB에 접속한다.
    C:\mysql\bin> mysql -u 유저명 -p db명
    Enter password: 패스워드입력
13) 테이블을 생성해서 사용한다
    create table 테이블명 (컬럼명 데이터형);


USER등록 및 DB등록 방법 ====================================

1) USER 등록
    mysql> insert into user values ('localhost', '유저명', password('패스워드'), 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y');

    위와 같이 쿼리를 실행하면 정해준 도메인에서 정해준 유저가 어떠한 DB에도 select, insert, update, delete, create, drop 등의 DB조작을 할 수 있게 된다. 즉 루트와 동일한 권한을 가지게 된다. default 로는 14개 모두 'n'이 설정된다.

    - values값 설명
    - 첫번째 항목 : 접속을 허가하는 컴퓨터의 도메인 명이나 IP
    - 두번째 항목 : 접속할 USER ID
    - 세번째 항목 : 패스워드
    - 세번째 이후 : 모든 DB에 대해 사용할 수 있는 SQL 명령어 종류

2) DB 등록
    mysql> insert into db values ('localhost', 'db명', '유저명', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y');

    위와 같이 쿼리를 실행하면 DB 별로 접근 가능한 USER를 설정하고 select, insert, update, delete, create, drop 등의 DB 조작 권한을 부여하게 된다. 즉 각 DB별 접근을 허가하는 USER와 USER의 권한을 설정한다. default 로는 10개 모두 'n'이 설정된다.

    - values값 설명
    - 첫번째 항목 : 접속을 허가하는 컴퓨터의 도메인 명이나 IP
    - 두번째 항목 : 접근 가능 DB명
    - 세번째 항목 : 접속할 USER ID
    - 세번째 이후 : 현재의 DB에 대해 사용할 수 있는 SQL 명령어 종류