신비한 비

select name from `user`
group by name;

select name,group_concat(code) from `user`
group by name;

select * from brand where name like '%석삼%' 
order by char_length(name) asc limit 5;

select * from brand where name like '%석삼%' 
order by char_length(name) asc, locate('석삼',name) asc limit 5,5;

update brand set name=REPLACE(name,'A','B') 
where id=1;

update brand set name=REPLACE(name,' ','') where name like ' %';
update brand set name=REPLACE(name,' ','') where name like '% ';

select now() from brand limit 1;

select now(3) from brand limit 1;

INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`) 
VALUES (5, '108', '석삼', '2022-12-21 19:42:21');

INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`) 
select null,code,name,now(3) from `order` where code in ('004','005');

INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`) 
VALUES (123, '108', '석삼', now(3));

INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`) 
select null,'108', '석삼',now(3) 
from dual where  not exists (select * from `brand` where name='석삼');

INSERT ignore INTO `brand`(`id`, `code`, `name`, `edit_date`) 
VALUES (123, '108', '석삼', now(3));

begin;
select * from `user` where id=1 
for update;
// .....
// 업무로직 처리
// .....
update `user` set score=score-1 where id=1;
commit;

INSERT  INTO `brand`(`id`, `code`, `name`, `edit_date`) 
VALUES (123, '108', '석삼', now(3))
on duplicate key update name='석삼',edit_date=now(3);

desc `order`;

show index from `order`;

show create table `order`;

create table order_2022121819 like `order`;

insert into order_2022121819 select * from `order`;

explain select * from `order` where code='002';

mysql> show processlist;
+---------+------+-----------+-------+---------+------+-------+------------------+
| Id      | User | Host      | db    | Command | Time | State | Info             |
+---------+------+-----------+-------+---------+------+-------+------------------+
| 2212724 | root | localhost | test  | Sleep   | 70   |       | NULL             |
| 2212286 | root | localhost | NULL  | Query   | 0    | NULL  | show processlist |
+---------+------+-----------+-------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysql> kill 2212724;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+---------+------+-----------+-------+---------+------+-------+------------------+
| Id      | User | Host      | db    | Command | Time | State | Info             |
+---------+------+-----------+-------+---------+------+-------+------------------+
| 2212286 | root | localhost | NULL  | Query   | 0    | NULL  | show processlist |
+---------+------+-----------+-------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysqldump -h 172.0.0.1 -u root -proot dbname > backup.sql​

뭐 너무 유명한? 명령어라 어느정도 개발?운영?서버 만져본 분이라면 다 아는 ...

이글의 타이틀과 약간 어긋난 놈이긴한데 일단 적어본다.

끝!

mysql 8.0 sequel Pro 접속 안될때. (ssl 이슈 이므로 , 아래 명령어 통해서 임시해결가능)

1. 도커 접속

# 실패!  localhost --> % 로 해야 됨.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

# 성공!
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

끝!

에러: Mysql Incorrect string value:xxxxxx

create table authorization_consent
(
    registered_client_id varchar(100)  not null,
    principal_name       varchar(200)  not null,
    authorities          varchar(1000) not null,
    primary key (registered_client_id, principal_name)
)ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

해결: 테이블 생성 퀄리문에 CHARACTER SET = utf8 COLLATE = utf8_general_ci  utf8 설정을 문구를 추가 한다.

잘되었는지도 한번 확인 해볼 필요가 있다.

끝!

mysql 에서 DDL.sql 수행시 아래와 같은 에러가 발생했다.

에러: invalid default value for ' 컬럼명 ' ....

create table authorization
(
    id                            varchar(100)                            not null
        primary key,
    registered_client_id          varchar(100)                            not null,
    principal_name                varchar(200)                            not null,
    authorization_grant_type      varchar(100)                            not null,
    attributes                    text                                    null,
    state                         varchar(500)                            null,
    authorization_code_value      text                                    null,
    authorization_code_issued_at  timestamp default CURRENT_TIMESTAMP     null
    // 생략 ...
);

해결방법:

SET @@session.sql_mode ="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION".

혹은 

SET @@global.sql_mode ="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION".

끝!