MySQL의 숨겨진 기능
저는 수년 동안 Microsoft SQL Server 로 작업 해 왔지만 최근에야 웹 응용 프로그램에 MySQL 을 사용하기 시작했고 지식에 굶주려 있습니다.
긴 줄의 "숨겨진 기능"질문 을 계속하기 위해이 오픈 소스 데이터베이스에 대한 지식을 향상시킬 MySQL의 숨겨진 기능이나 편리한 기능을 알고 싶습니다.
현상금을 내 셨으니 힘들게 얻은 비밀을 공유하겠습니다 ...
일반적으로 오늘 조정 한 모든 SQL은 하위 쿼리를 사용해야합니다. Oracle 데이터베이스 세계에서 왔기 때문에 당연한 것으로 여겨지는 것은 MySQL에서 동일하게 작동하지 않았습니다. 그리고 MySQL 튜닝에 대해 읽었을 때 쿼리 최적화 측면에서 MySQL이 Oracle 뒤에 있다는 결론을 내 렸습니다.
대부분의 B2C 애플리케이션에 필요한 간단한 쿼리는 MySQL에서 잘 작동 할 수 있지만, Intelligence Reporting에 필요한 대부분의 집계보고 쿼리 유형은 MySQL이 쿼리를 더 빠르게 실행하도록 안내하기 위해 SQL 쿼리를 상당히 계획하고 재구성해야하는 것 같습니다.
관리:
max_connections동시 연결 수입니다. 기본값은 100 개 연결 (5.0부터 151 개)이며 매우 작습니다.
노트 :
연결은 메모리를 차지하고 OS는 많은 연결을 처리하지 못할 수 있습니다.
Linux / x86 용 MySQL 바이너리를 사용하면 최대 4096 개의 동시 연결이 가능하지만 자체 컴파일 된 바이너리는 종종 제한이 적습니다.
열린 테이블 및 동시 연결 수와 일치하도록 table_cache를 설정하십시오. open_tables 값을 확인하고 빠르게 증가하는 경우 크기를 늘려야합니다.
노트 :
이전 2 개의 매개 변수에는 많은 열린 파일이 필요할 수 있습니다. 20 + max_connections + table_cache * 2는 필요한 것에 대한 좋은 추정치입니다. Linux의 MySQL에는 open_file_limit 옵션이 있으며이 제한을 설정합니다.
복잡한 쿼리가있는 경우 sort_buffer_size 및 tmp_table_size가 매우 중요 할 수 있습니다. 값은 쿼리 복잡성과 사용 가능한 리소스에 따라 다르지만 각각 4Mb 및 32Mb가 권장되는 시작점입니다.
참고 : 이는 read_buffer_size, read_rnd_buffer_size 등의 "연결 당"값이며, 이는 각 연결에이 값이 필요할 수 있음을 의미합니다. 따라서 이러한 매개 변수를 설정할 때 부하 및 사용 가능한 리소스를 고려하십시오. 예를 들어 sort_buffer_size는 MySQL이 정렬을 수행해야하는 경우에만 할당됩니다. 참고 : 메모리가 부족하지 않도록주의하십시오.
많은 연결이 설정된 경우 (즉, 지속적인 연결이없는 웹 사이트) thread_cache_size를 0이 아닌 값으로 설정하여 성능을 향상시킬 수 있습니다. 16은 시작하기에 좋은 값입니다. threads_created가 빠르게 증가하지 않을 때까지 값을 늘리십시오.
기본 키 :
테이블 당 AUTO_INCREMENT 열은 하나만있을 수 있으며 인덱싱되어야하며 DEFAULT 값을 가질 수 없습니다.
KEY는 일반적으로 INDEX의 동의어입니다. 키 속성 PRIMARY KEY는 열 정의에 제공 될 때 KEY로 지정 될 수도 있습니다. 이것은 다른 데이터베이스 시스템과의 호환성을 위해 구현되었습니다.
PRIMARY KEY는 모든 키 열이 NOT NULL로 정의되어야하는 고유 인덱스입니다.
PRIMARY KEY 또는 UNIQUE 인덱스가 정수 유형을 가진 하나의 열로만 구성된 경우 SELECT 문에서 해당 열을 "_rowid"로 참조 할 수도 있습니다.
MySQL에서 PRIMARY KEY의 이름은 PRIMARY입니다.
현재 InnoDB (v5.1?) 테이블 만 외래 키를 지원합니다.
일반적으로 테이블을 생성 할 때 필요한 모든 인덱스를 생성합니다. PRIMARY KEY, KEY, UNIQUE 또는 INDEX로 선언 된 모든 열이 인덱싱됩니다.
NULL은 "값이 없음"을 의미합니다. NULL을 테스트하기 위해 =, <또는 <>와 같은 산술 비교 연산자를 사용할 수 없습니다 . 대신 IS NULL 및 IS NOT NULL 연산자를 사용하십시오.
NO_AUTO_VALUE_ON_ZERO는 0에 대한 자동 증가를 억제하므로 NULL 만 다음 시퀀스 번호를 생성합니다. 이 모드는 테이블의 AUTO_INCREMENT 열에 0이 저장된 경우 유용 할 수 있습니다. (0 저장은 권장되지 않습니다.)
새 행에 사용할 AUTO_INCREMENT 카운터 값을 변경하려면 다음을 수행하십시오.
ALTER TABLE mytable AUTO_INCREMENT = value;
또는 SET INSERT_ID = value;
달리 지정하지 않는 한 값은 1000000으로 시작하거나 다음과 같이 지정합니다.
...) ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT = 1
타임 스탬프 :
TIMESTAMP 열의 값은 저장을 위해 현재 시간대에서 UTC로, 검색을 위해 UTC에서 현재 시간대로 변환됩니다.
http://dev.mysql.com/doc/refman/5.1/en/timestamp.html 테이블의 하나의 TIMESTAMP 열에 대해 현재 타임 스탬프를 기본값 및 자동 업데이트 값으로 할당 할 수 있습니다.
WHERE 절에서 이러한 유형 중 하나를 사용할 때주의해야 할 한 가지는 WHERE UNIX_TIMESTAMP (datecolumn) = 1057941242가 아닌 WHERE datecolumn = FROM_UNIXTIME (1057941242)을 수행하는 것이 가장 좋습니다. 후자를 수행하면 인덱스가 활용되지 않습니다. 그 칼럼에.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
UNIX_TIMESTAMP()
FROM_UNIXTIME()
UTC_DATE()
UTC_TIME()
UTC_TIMESTAMP()
MySQL에서 datetime을 유닉스 타임 스탬프로 변환하면 :
그런 다음 여기에 24 시간을 추가합니다.
그런 다음 다시 datetime으로 변환하면 마술처럼 한 시간이 줄어 듭니다!
여기에 무슨 일이 일어나고 있는지입니다. 유닉스 타임 스탬프를 다시 datetime으로 변환 할 때 시간대가 고려되고 2006 년 10 월 28 일과 29 일 사이에 일광 절약 시간이 중단되고 한 시간이 손실되었습니다.
MySQL 4.1.3부터 CURRENT_TIMESTAMP (), CURRENT_TIME (), CURRENT_DATE () 및 FROM_UNIXTIME () 함수 는 time_zone 시스템 변수 값으로 사용할 수 있는 연결의 현재 시간대 값을 반환합니다 . 또한 UNIX_TIMESTAMP ()는 인수가 현재 시간대의 날짜 / 시간 값이라고 가정합니다.
현재 시간대 설정은 UTC_TIMESTAMP ()와 같은 함수 또는 DATE, TIME 또는 DATETIME 열의 값에 의해 표시되는 값에 영향을주지 않습니다.
참고 : ON UPDATE 는 필드가 변경된 경우 에만 DateTime을 업데이트합니다. 업데이트 결과 필드가 변경되지 않으면 DateTime이 업데이트되지 않습니다!
또한 첫 번째 TIMESTAMP는 지정되지 않은 경우에도 기본적으로 항상 AUTOUPDATE입니다.
Dates로 작업 할 때 데이터 수학은 정수를 더하거나 빼는 간단한 문제이기 때문에 거의 항상 Julian Date를 사용하고 같은 이유로 자정 이후의 초를 사용합니다. 초보다 더 세밀한 시간 resoultion이 필요한 경우는 드뭅니다.
둘 다 4 바이트 정수로 저장할 수 있으며 공간이 정말 빡빡한 경우 UNIX 시간 (1970 년 1 월 1 일 이후 초)으로 결합 할 수 있습니다. 이는 다음과 같이 약 2106 년까지 괜찮을 것입니다.
'초 (24 시간) = 86400
'부호있는 정수 최대 값 = 2,147,483,647-68 년의 초를 저장할 수 있습니다.
'부호없는 정수 최대 값 = 4,294,967,295-136 년의 초를 저장할 수 있습니다.
바이너리 프로토콜 :
MySQL 4.1은 문자열 형식과의 변환없이 문자열이 아닌 데이터 값을 기본 형식으로 보내고 반환 할 수있는 바이너리 프로토콜을 도입했습니다. (매우 유용함)
또한 mysql_real_query ()는 명령문 문자열에서 작동하기 위해 strlen ()을 호출하지 않기 때문에 mysql_query ()보다 빠릅니다.
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html 바이너리 프로토콜은 서버 측 준비된 명령문을 지원하고 원시 형식으로 데이터 값을 전송할 수 있습니다. 바이너리 프로토콜은 MySQL 4.1의 이전 릴리스에서 상당한 수정을 거쳤습니다.
IS_NUM () 매크로를 사용하여 필드에 숫자 유형이 있는지 테스트 할 수 있습니다. 유형 값을 IS_NUM ()에 전달하고 필드가 숫자이면 TRUE로 평가됩니다.
한 가지 주목할 점은 이진 데이터 를 이스케이프 처리하고 MySQL이 백 슬래시와 따옴표 문자 만 이스케이프해야한다는 점을 기억하면 일반 쿼리 내에서 보낼 수 있다는 것입니다. 예를 들어 암호화 / Salted 암호와 같은 짧은 이진 문자열을 삽입하는 것은 정말 쉬운 방법입니다.
마스터 서버 :
http://www.experts-exchange.com/Database/MySQL/Q_22967482.html
http://www.databasejournal.com/features/mysql/article.php/10897_3355201_2
GRANT REPLICATION SLAVE ON . slave_user IDENTIFIED by 'slave_password'
#Master Binary Logging Config STATEMENT causes replication
to be statement-based - default
log-bin=Mike
binlog-format=STATEMENT
server-id=1
max_binlog_size = 10M
expire_logs_days = 120
#Slave Config
master-host=master-hostname
master-user=slave-user
master-password=slave-password
server-id=2
바이너리 로그 파일은 다음과 같아야합니다.
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
http://www.mydigitallife.info/2007/10/06/how-to-read-mysql-binary-log-files-binlog-with-mysqlbinlog/
http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html
RESET MASTER 문을 사용하여 모든 2 진 로그 파일을 삭제하거나 PURGE MASTER를 사용하여 일부를 삭제할 수 있습니다.
--result-file = binlog.txt TrustedFriend-bin.000030
표준화:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
UDF 기능
http://www.koders.com/cpp/fid10666379322B54AD41AEB0E4100D87C8CDDF1D8C.aspx
http://souptonuts.sourceforge.net/readme_mysql.htm
데이터 유형 :
http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html
http://www.informit.com/articles/article.aspx?p=1238838&seqNum=2
http://bitfilm.net/2008/03/24/saving-bytes-efficient-data-storage-mysql-part-1/
한 가지 유의해야 할 점은 CHAR와 VARCHAR가 모두있는 혼합 테이블에서 mySQL이 CHAR를 VARCHAR로 변경한다는 것입니다.
RecNum integer_type UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (RecNum)
MySQL은 표준 SQL 및 ISO 8601 사양에 따라 항상 연도 첫 번째 날짜를 나타냅니다.
기타 :
일부 MySQl 기능을 끄면 데이터 파일이 작아지고 액세스 속도가 빨라집니다. 예를 들면 :
--datadir은 데이터 디렉토리를 지정하고
--skip-innodb는 inno 옵션을 끄고 10-20M을 절약합니다.
자세한 내용은 http://dev.mysql.com/tech-resources/articles/mysql-c-api.html
7 장-무료 다운로드
InnoDB는 트랜잭션 적이지만 그와 함께 제공되는 성능 오버 헤드가 있습니다. MyISAM 테이블이 내 프로젝트의 90 %에 충분하다는 것을 알았습니다. 비 트랜잭션 안전 테이블 (MyISAM)에는 다음과 같은 이유로 발생하는 몇 가지 장점이 있습니다.
트랜잭션 오버 헤드가 없습니다.
훨씬 더 빨리
디스크 공간 요구 사항 감소
업데이트를 수행하는 데 필요한 메모리 감소
각 MyISAM 테이블은 디스크에 세 개의 파일로 저장됩니다. 파일 이름은 테이블 이름으로 시작하고 파일 유형을 나타내는 확장자가 있습니다. .frm 파일은 테이블 형식을 저장합니다. 데이터 파일의 확장자는 .MYD (MYData)입니다. 색인 파일의 확장자는 .MYI (MYIndex)입니다.
이러한 파일 은 시간이 많이 걸리는 MySQL 관리자 백업 기능을 사용하지 않고도 저장 위치에 그대로 복사 할 수 있습니다 (복원도 마찬가지 임).
트릭은 이러한 파일의 복사본을 만든 다음 테이블을 삭제하는 것입니다. 파일을 다시 넣으면 MySQl이 파일을 인식하고 테이블 추적을 업데이트합니다.
백업 / 복원해야하는 경우
백업을 복원하거나 기존 덤프 파일에서 가져 오는 것은 각 테이블에있는 인덱스 및 기본 키의 수에 따라 시간이 오래 걸릴 수 있습니다. 원본 덤프 파일을 다음으로 둘러 싸서 수정하면이 프로세스의 속도를 크게 높일 수 있습니다.
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
.. your dump file ..
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
재로드 속도를 크게 높이려면 SQL 명령 SET AUTOCOMMIT = 0을 추가하십시오. 덤프 파일의 시작 부분에 COMMIT를 추가하십시오. 끝까지 명령.
기본적으로 자동 커밋은 켜져 있습니다. 즉, 덤프 파일의 모든 삽입 명령이 별도의 트랜잭션으로 처리되고 다음 트랜잭션이 시작되기 전에 디스크에 기록됩니다. 이러한 명령을 추가하지 않으면 대용량 데이터베이스를 InnoDB로 다시로드하는 데 몇 시간이 걸릴 수 있습니다.
MySQL 테이블에서 행의 최대 크기는 65,535 바이트입니다.
MySQL 5.0.3 이상에서 VARCHAR의 유효 최대 길이 = 최대 행 크기 (65,535 바이트)
VARCHAR 값은 저장 될 때 채워지지 않습니다. 후행 공백은 표준 SQL에 따라 값이 저장 및 검색 될 때 유지됩니다.
MySQL의 CHAR 및 VARCHAR 값은 후행 공백에 관계없이 비교됩니다.
CHAR를 사용하면 전체 레코드가 고정 된 크기 인 경우에만 액세스 속도가 빨라집니다. 즉, 가변 크기 개체를 사용하는 경우 모두 가변 크기로 만드는 것이 좋습니다. VARCHAR도 포함하는 테이블에서 CHAR를 사용하여 속도를 얻지 못합니다.
MySQL 5.0.3부터 255 자의 VARCHAR 제한이 65535 자로 증가했습니다.
전체 텍스트 검색은 MyISAM 테이블에 대해서만 지원됩니다.
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
BLOB 열에는 문자 집합이 없으며 정렬 및 비교는 열 값의 바이트 숫자 값을 기반으로합니다.
엄격한 SQL 모드를 사용하지 않고 열의 최대 길이를 초과하는 BLOB 또는 TEXT 열에 값을 할당하면 값이 잘려서 경고가 생성됩니다.
유용한 명령 :
엄격 모드 확인 : SELECT @@ global.sql_mode;
엄격 모드 끄기 :
SET @@ global.sql_mode = '';
SET @@ global.sql_mode = 'MYSQL40'
또는 제거 : sql-mode = "STRICT_TRANS_TABLES, ...
다음에서 열 표시 mytable
virtualcolumnmytable ORDER BY virtualcolumn에서 max (namecount)를 선택하십시오.
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id last_insert_id ()
현재 스레드에 삽입 된 마지막 행의 PK를 가져옵니다. max (pkcolname)은 전체적으로 마지막 PK를 가져옵니다.
참고 : 테이블이 비어있는 경우 max (pkcolname)은 1을 반환합니다. mysql_insert_id ()는 네이티브 MySQL C API 함수 mysql_insert_id ()의 반환 유형을 long 유형 (PHP에서 int로 명명)으로 변환합니다.
AUTO_INCREMENT 열에 BIGINT 열 유형이있는 경우 mysql_insert_id ()에서 반환 된 값이 올바르지 않습니다. 대신 SQL 쿼리에서 내부 MySQL SQL 함수 LAST_INSERT_ID ()를 사용하십시오.
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
테이블에 데이터를 삽입하려고 할 때 오류가 발생한다는 점에 유의하십시오.
Unknown column ‘the first bit of data what you want to put into the table‘ in ‘field list’
같은 것을 사용하여
INSERT INTO table (this, that) VALUES ($this, $that)
테이블에 붙이려는 값 주위에 아포스트로피가 없기 때문입니다. 따라서 코드를 다음과 같이 변경해야합니다.
INSERT INTO table (this, that) VALUES ('$this', '$that')
``는 값이 아닌 MySQL 필드, 데이터베이스 또는 테이블을 정의하는 데 사용됩니다.)
쿼리 중 서버 연결이 끊어졌습니다.
http://dev.mysql.com/doc/refman/5.1/en/gone-away.html
http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
http://dev.mysql.com/doc/refman/5.1/en/show-variables.html
http://dev.mysql.com/doc/refman/5.1/en/option-files.html
http://dev.mysql.com/doc/refman/5.1/en/error-log.html
쿼리 튜닝
http://www.artfulsoftware.com/infotree/queries.php?&bw=1313
제가 생각하기에 보너스를 받기에 충분 합니다. 훌륭한 무료 데이터베이스를 가진 많은 시간과 많은 프로젝트의 결실입니다 . 주로 MySQL을 사용하여 Windows 플랫폼에서 응용 프로그램 데이터 서버를 개발합니다. 내가 정리해야했던 최악의 문제는
여기에 언급 된 많은 트릭을 사용하여 테이블을 유용한 것으로 처리하려면 일련의 응용 프로그램이 필요했습니다.
이 내용이 놀랍도록 도움이 되었다면 투표하여 감사를 표하십시오.
www.coastrd.com에서 다른 기사와 백서를 확인하십시오.
MySQL을 너무 숨겨져 기능 중 하나는 SQL의 준수되는 정말 좋지 않다라는 것이다, 음, 버그 정말하지만, 더 망 가지고 ... :-)
현재 캐시에있는 테이블을 확인하는 명령 :
mysql> SHOW open TABLES FROM test;
+----------+-------+--------+-------------+
| DATABASE | TABLE | In_use | Name_locked |
+----------+-------+--------+-------------+
| test | a | 3 | 0 |
+----------+-------+--------+-------------+
1 row IN SET (0.00 sec)
( MySQL 성능 블로그에서 )
누가 무엇을하고 있는지 알아내는 명령 :
mysql> show processlist;
show processlist;
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
| 1 | root | localhost:32893 | NULL | Sleep | 0 | | NULL |
| 5 | system user | | NULL | Connect | 98 | Waiting for master to send event | NULL |
| 6 | system user | | NULL | Connect | 5018 | Reading event from the relay log | NULL |
+-----+------+-----------+---------+---------+-------+-------+------------------+
3 rows in set (0.00 sec)
그리고 다음을 사용하여 프로세스를 종료 할 수 있습니다.
mysql>kill 5
특히 MySQL의 inet_ntoa()및 inet_aton(). 테이블의 IP 주소를 매우 간단하게 처리합니다 (적어도 IPv4 주소 만있는 한!).
나는 on duplicate key느리게 만들어진 모든 종류의 카운터에 대해 (일명 upsert, merge) 좋아합니다 .
insert into occurances(word,count) values('foo',1),('bar',1)
on duplicate key cnt=cnt+1
하나의 쿼리에 여러 행을 삽입하고 각 행에 대해 중복 인덱스를 즉시 처리 할 수 있습니다.
다시 말하지만 숨겨진 기능은 아니지만 정말 편리합니다.
특색
쉽게 DDL 가져 오기 :
SHOW CREATE TABLE CountryLanguage
산출:
CountryLanguage | CREATE TABLE countrylanguage (
CountryCode char(3) NOT NULL DEFAULT '',
Language char(30) NOT NULL DEFAULT '',
IsOfficial enum('T','F') NOT NULL DEFAULT 'F',
Percentage float(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (CountryCode,Language)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
기능 : GROUP_CONCAT () 집계 함수 세부 사항별로 인수의 연결된 문자열을 만들고 그룹별로 연결하여 집계합니다.
예 1 : 단순
SELECT CountryCode
, GROUP_CONCAT(Language) AS List
FROM CountryLanguage
GROUP BY CountryCode
산출:
+-------------+------------------------------------+
| CountryCode | List |
+-------------+------------------------------------+
| ABW | Dutch,English,Papiamento,Spanish |
. ... . ... .
| ZWE | English,Ndebele,Nyanja,Shona |
+-------------+------------------------------------+
예 2 : 여러 인수
SELECT CountryCode
, GROUP_CONCAT(
Language
, IF(IsOfficial='T', ' (Official)', '')
) AS List
FROM CountryLanguage
GROUP BY CountryCode
산출:
+-------------+---------------------------------------------+
| CountryCode | List |
+-------------+---------------------------------------------+
| ABW | Dutch (Official),English,Papiamento,Spanish |
. ... . ... .
| ZWE | English (Official),Ndebele,Nyanja,Shona |
+-------------+---------------------------------------------+
예 3 : 사용자 지정 구분 기호 사용
SELECT CountryCode
, GROUP_CONCAT(Language SEPARATOR ' and ') AS List
FROM CountryLanguage
GROUP BY CountryCode
산출:
+-------------+----------------------------------------------+
| CountryCode | List |
+-------------+----------------------------------------------+
| ABW | Dutch and English and Papiamento and Spanish |
. ... . ... .
| ZWE | English and Ndebele and Nyanja and Shona |
+-------------+----------------------------------------------+
예제 4 : 목록 요소의 순서 제어
SELECT CountryCode
, GROUP_CONCAT(
Language
ORDER BY CASE IsOfficial WHEN 'T' THEN 1 ELSE 2 END DESC
, Language
) AS List
FROM CountryLanguage
GROUP BY CountryCode
산출:
+-------------+------------------------------------+
| CountryCode | List |
+-------------+------------------------------------+
| ABW | English,Papiamento,Spanish,Dutch, |
. ... . ... .
| ZWE | Ndebele,Nyanja,Shona,English |
+-------------+------------------------------------+
기능 : 여러 표현식이있는 COUNT (DISTINCT)
COUNT (DISTINCT ...) 표현식에 여러 표현식을 사용하여 조합 수를 계산할 수 있습니다.
SELECT COUNT(DISTINCT CountryCode, Language) FROM CountryLanguage
Feature / Gotcha : GROUP BY 목록에 집계되지 않은 표현식을 포함 할 필요가 없습니다.
대부분의 RDBMS-es는 SELECT 목록의 집계되지 않은 모든 표현식이 GROUP BY에 나타나도록 요구하는 SQL92 호환 GROUP BY를 적용합니다. 이러한 RDBMS-es에서이 문은 다음과 같습니다.
SELECT Country.Code, Country.Continent, COUNT(CountryLanguage.Language)
FROM CountryLanguage
INNER JOIN Country
ON CountryLanguage.CountryCode = Country.Code
GROUP BY Country.Code
is not valid, because the SELECT list contains the non-aggregated column Country.Continent which does not appear in the GROUP BY list. In these RDBMS-es, you must either modify the GROUP BY list to read
GROUP BY Country.Code, Country.Continent
or you must add some non-sense aggregate to Country.Continent, for example
SELECT Country.Code, MAX(Country.Continent), COUNT(CountryLanguage.Language)
Now, the thing is, logically there is nothing that demands that Country.Continent should be aggreagated. See, Country.Code is the primary key of the Country table. Country.Continent is also a column from the Country table and is thus by definitions functionally dependent upon the primary key Country.Code. So, there must exist exactly one value in Country.Continent for each distinct Country.Code. If you realize that, than you realize that it does not make sense to aggregate it (there is just one value, right) nor to group by it (as it won't make the result more unique as you're already grouping by on the pk)
Anyway - MySQL lets you include non-aggregated columns in the SELECT list without requiring you to also add them to the GROUP BY clause.
The gotcha with this is that MySQL does not protect you in case you happen to use a non-aggregated column. So, a query like this:
SELECT Country.Code, COUNT(CountryLanguage.Language), CountryLanguage.Percentage
FROM CountryLanguage
INNER JOIN Country
ON CountryLanguage.CountryCode = Country.Code
GROUP BY Country.Code
Will be executed without complaint, but the CountryLanguage.Percentage column will contain non-sense (that is to say, of all languages percentages, one of the available values for the percentage will be picked at random or at least outside your control.
The "pager" command in the client
If you've got, say, 10,000 rows in your result and want to view them (This assumes the "less" and "tee" commands available, which is normally the case under Linux; in Windows YMMV.)
pager less
select lots_of_stuff FROM tbl WHERE clause_which_matches_10k_rows;
And you'll get them in the "less" file viewer so you can page through them nicely, search etc.
Also
pager tee myfile.txt
select a_few_things FROM tbl WHERE i_want_to_save_output_to_a_file;
Will conveniently write to a file.
Some things you may find interesting:
<query>\G -- \G in the CLI instead of the ; will show one column per row
explain <query>; -- this will show the execution plan for the query
Not a hidden feature, but useful nonetheless: http://mtop.sourceforge.net/
Here are some of my tips - I blogged about them in my blog (Link)
- You don't need to use '@' sign when declaring variables.
- You have to use a delimiter (the default is ';') to demarcate the end of a statement - Link
- If you trying to move data between MS-SQL 2005 and mySQL there are a few hoops to jump through - Link
- Doing case sensitive matches in mySQL - link
If you're going to be working with large and/or high transaction InnoDb databases learn and understand "SHOW INNODB STATUS" Mysql Performance Blog, it will become your friend.
If using cmdline Mysq, you can interact with the command line (on Linux machines - not sure if there is an equivalent effect on Windows) by using the shriek/exclamation mark. For example:
\! cat file1.sql
will display the code for file1.sql. To save your statement and query to a file, use the tee facility
\T filename
to turn this off use \t
Lastly to run a script you've already saved, use "source filename". Of course, the normal alternative is to direct in the script name when starting mysql from the command line:
mysql -u root -p < case1.sql
Hope that's of use to someone !
Edit: Just remembered another one - when invoking mysql from the command line you can use the -t switch so that output is in table format - a real boon with some queries (although of course terminating queries with \G as mentioned elsewhere here is also helpful in this respect). A lot more on various switches Command Line Tool
Just found out a neat way to change the order of a sort (normally use Case...) If you want to change the order of a sort (perhaps sort by 1, 4, 3 ,2 instead of 1, 2, 3,4) you can use the field function within the Order by clause. For example
Order By Field(sort_field,1,4,3,2)
- Found this out here Order by day_of_week in MySQL courtesey of user gms8994
I don't think this is MySQL specific, but enlighting for me:
Instead of writing
WHERE (x.id > y.id) OR (x.id = y.id AND x.f2 > y.f2)
You can just write
WHERE (x.id, x.f2) > (y.id, y.f2)
mysqlsla - One of the very commonly used slow query log analysis tool. You can see top 10 worsts queries since u last rolled out slow query logs. It can also tell you the number of times that BAD query was fired and how much total time it took on the server.
Actually documented, but very annoying: automatic conversions for incorrect dates and other incorrect input.
Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values and coerces them to legal values for data entry. In MySQL 5.0.2 and up, that remains the default behavior, but you can change the server SQL mode to select more traditional treatment of bad values such that the server rejects them and aborts the statement in which they occur.
As for dates: sometimes you'll be "lucky" when MySQL doesn't adjust the input to nearby valid dates, but instead stores it as 0000-00-00 which by definition is invalid. However, even then you might have wanted MySQL to fail rather than silently storing this value for you.
The built-in SQL Profiler.
InnoDB by default stores all tables in one global tablespace that will never shrink.
You can use innodb_file_per_table which will put each table in a separate tablespace that will be deleted when you drop the table or database.
Plan ahead for this since you have to dump and restore the database to reclaim space otherwise.
If you insert into datetime column empty string value "", MySQL will retain the value as 00/00/0000 00:00:00. Unlike Oracle, which will save null value.
During my benchmarks with large datasets and DATETIME fields, it's always slower to do this query:
SELECT * FROM mytable
WHERE date(date_colum) BETWEEN '2011-01-01' AND ''2011-03-03';
Than this approach:
SELECT * FROM mytable
WHERE date_column BETWEEN '2011-01-01 00:00:00' AND '2011-03-03 23:59:59'
참고URL : https://stackoverflow.com/questions/368858/hidden-features-of-mysql
'program story' 카테고리의 다른 글
| 파이썬에서 nosetest / unittest로 출력을 어설 션하는 방법은 무엇입니까? (0) | 2020.08.17 |
|---|---|
| TabLayout을 사용할 때 탭 배경색을 어떻게 변경합니까? (0) | 2020.08.17 |
| 부트 스트랩 모달이 열려 있는지 확인하는 방법으로 jquery validate를 사용할 수 있습니다. (0) | 2020.08.17 |
| rubytutorial을 따르려는 Windows의 ExecJS :: RuntimeError (0) | 2020.08.17 |
| ConcurrentDictionary AddOrUpdate에서 업데이트 부분에 추가 할 항목 (0) | 2020.08.17 |