MySQL에서 하나를 제외한 모든 열을 선택 하시겠습니까?
select 문을 사용하여 하나를 제외한 특정 MySQL 테이블에서 모든 열을 가져 오려고합니다. 이를 수행하는 간단한 방법이 있습니까?
편집 :이 테이블에는 53 개의 열이 있습니다 (NOT MY DESIGN)
실제로 방법이 있습니다.이 작업을 수행하려면 물론 권한이 있어야합니다 ...
SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_omit>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>' AND TABLE_SCHEMA = '<database>'), ' FROM <table>');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
교체 <table>, <database> and <columns_to_omit>
mysql 정의 (수동)에는 그런 것이 없습니다. 그러나 실제로 많은 수의 열이있는 col1경우 ..., col100다음이 유용 할 수 있습니다.
mysql> CREATE TEMPORARY TABLE temp_tb SELECT * FROM orig_tb;
mysql> ALTER TABLE temp_tb DROP col_x;
mysql> SELECT * FROM temp_tb;
이 경우 View가 더 잘 작동합니까?
CREATE VIEW vwTable
as
SELECT
col1
, col2
, col3
, col..
, col53
FROM table
넌 할 수있어:
SELECT column1, column2, column4 FROM table WHERE whatever
column3을 얻지 않고 더 일반적인 해결책을 찾고 있었습니까?
보안 문제 / 민감한 정보 등 필드 값을 제외하려는 경우 해당 열을 null로 검색 할 수 있습니다.
예 :
SELECT *, NULL AS salary FROM users
내가 아는 한, 그렇지 않습니다. 당신은 다음과 같은 것을 할 수 있습니다 :
SELECT col1, col2, col3, col4 FROM tbl
원하는 열을 수동으로 선택하십시오. 그러나 많은 열을 원한다면 다음과 같이하십시오.
SELECT * FROM tbl
원하지 않는 것을 무시하십시오.
귀하의 특별한 경우에는 다음과 같이 제안합니다.
SELECT * FROM tbl
몇 개의 열만 원하지 않는 한. 네 개의 열만 원하면 다음을 수행하십시오.
SELECT col3, col6, col45, col 52 FROM tbl
괜찮지 만 50 열을 원한다면 쿼리를 만드는 코드가 너무 읽기 어려워집니다.
@Mahomedalid와 @Junaid의 솔루션을 시도하는 동안 문제가 발견되었습니다. 공유를 생각했습니다. 열 이름에 체크인과 같은 공백이나 하이픈이 있으면 쿼리가 실패합니다. 간단한 해결 방법은 열 이름 주위에 백틱을 사용하는 것입니다. 수정 된 검색어는 다음과 같습니다.
SET @SQL = CONCAT('SELECT ', (SELECT GROUP_CONCAT(CONCAT("`", COLUMN_NAME, "`")) FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND COLUMN_NAME NOT IN ('id')), ' FROM users');
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;
선택하지 않으려는 열에 많은 양의 데이터가 있고 속도 문제로 인해 포함하지 않으려는 경우 다른 열을 자주 선택하면 새 테이블을 만드는 것이 좋습니다. 일반적으로 원래 테이블의 키로 선택하지 않은 하나의 필드를 사용하여 원래 테이블에서 필드를 제거하십시오. 추가 필드가 실제로 필요한 경우 테이블을 조인하십시오.
DESCRIBE my_table 을 사용하고 그 결과를 사용하여 SELECT 문을 동적으로 생성 할 수 있습니다 .
내 주요 문제는 테이블을 조인 할 때 얻는 많은 열입니다. 이것은 귀하의 질문에 대한 답변이 아니지만 ( 하나의 테이블 에서 특정 열을 제외한 모든 열을 선택하는 방법)을 지정 하는 대신 특정 테이블에서 모든 열을 가져 오도록 지정할 수 있다고 언급 할 가치가 있다고 생각합니다 .table.
이것이 매우 유용한 방법의 예는 다음과 같습니다.
사용자를 선택합니다. *, phone.meta_value (전화), zipcode.meta_value (우편 번호) 사용자로부터 전화로 user_meta 가입 on ((users.user_id = phone.user_id) AND (phone.meta_key = 'phone')) 우편 번호로 user_meta를 왼쪽으로 조인 on ((users.user_id = zipcode.user_id) AND (zipcode.meta_key = 'zipcode'))
결과는 users 테이블의 모든 열과 메타 테이블에서 조인 된 두 개의 추가 열입니다.
@Mahomedalid의견에서 정보를 얻은이 사실 외에도 답변을 좋아했습니다 @Bill Karwin. 제기 된 가능한 문제 @Jan Koritak는 사실에 직면했지만 그에 대한 트릭을 발견하고 문제에 직면 한 모든 사람들을 위해 여기에 공유하고 싶습니다.
다음과 같이 REPLACE 함수를 Prepared 문의 하위 쿼리에서 where 절로 바꿀 수 있습니다.
내 테이블 및 열 이름 사용
SET @SQL = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND COLUMN_NAME NOT IN ('id')), ' FROM users');
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;
따라서 이것은 필드 id만 제외 하지만 제외 하지는 않습니다.company_id
이것이 해결책을 찾는 사람에게 도움이되기를 바랍니다.
문안 인사
모든 열을 쿼리하더라도 쿼리중인 열을 지정하는 것이 좋습니다.
따라서 명세서에 각 열의 이름을 작성하는 것이 좋습니다 (원하지 않는 열 제외).
SELECT
col1
, col2
, col3
, col..
, col53
FROM table
그냥 해
SELECT * FROM table WHERE whatever
그런 다음 좋아하는 프로그래밍 언어로 열을 삭제하십시오 : php
while (($data = mysql_fetch_array($result, MYSQL_ASSOC)) !== FALSE) {
unset($data["id"]);
foreach ($data as $k => $v) {
echo"$v,";
}
}
모든 열을 나열하는 "간단한"솔루션에 동의하지만 이는 부담이 될 수 있으며 오타로 인해 많은 시간이 낭비 될 수 있습니다. "getTableColumns"함수를 사용하여 쿼리에 붙여 넣기에 적합한 열 이름을 검색합니다. 그런 다음 내가 원하지 않는 것을 삭제하기 만하면됩니다.
CREATE FUNCTION `getTableColumns`(tablename varchar(100))
RETURNS varchar(5000) CHARSET latin1
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE res VARCHAR(5000) DEFAULT "";
DECLARE col VARCHAR(200);
DECLARE cur1 CURSOR FOR
select COLUMN_NAME from information_schema.columns
where TABLE_NAME=@table AND TABLE_SCHEMA="yourdatabase" ORDER BY ORDINAL_POSITION;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO col;
IF NOT done THEN
set res = CONCAT(res,IF(LENGTH(res)>0,",",""),col);
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
RETURN res;
결과는 예를 들어 쉼표로 구분 된 문자열을 반환합니다.
col1, col2, col3, col4, ... col53
Select *다른 곳에서 언급했듯이 필요하지 않은 것이 BLOB 인 경우 오버 헤드 크립을 원치 않는 것으로 충분합니다 .
필요한 데이터 가 포함 된 뷰 를 만든 다음 Select *데이터베이스 소프트웨어가 지원하는 경우 편안하게 사용할 수 있습니다. 그렇지 않으면 큰 데이터를 다른 테이블에 넣으십시오.
처음에는 정규 표현식을 사용할 수 있다고 생각했지만 MYSQL 문서를 읽으면서 할 수없는 것 같습니다. 내가 당신이라면 다른 언어 (예 : PHP)를 사용하여 얻으려는 열 목록을 생성하고 문자열로 저장 한 다음 SQL을 생성하는 데 사용하십시오.
나는 이것을 원했기 때문에 대신 함수를 만들었습니다.
public function getColsExcept($table,$remove){
$res =mysql_query("SHOW COLUMNS FROM $table");
while($arr = mysql_fetch_assoc($res)){
$cols[] = $arr['Field'];
}
if(is_array($remove)){
$newCols = array_diff($cols,$remove);
return "`".implode("`,`",$newCols)."`";
}else{
$length = count($cols);
for($i=0;$i<$length;$i++){
if($cols[$i] == $remove)
unset($cols[$i]);
}
return "`".implode("`,`",$cols)."`";
}
}
작동 방식은 테이블을 입력하고 원하지 않는 열이나 배열에 입력하는 것입니다. array ( "id", "name", "whatevercolumn")
그래서 select에서 다음과 같이 사용할 수 있습니다 :
mysql_query("SELECT ".$db->getColsExcept('table',array('id','bigtextcolumn'))." FROM table");
또는
mysql_query("SELECT ".$db->getColsExcept('table','bigtextcolumn')." FROM table");
@Mahomedalid의 답변에 동의하십시오. 그러나 나는 준비된 진술과 같은 것을하고 싶지 않았고 모든 필드를 입력하고 싶지 않았습니다. 그래서 내가 가진 것은 어리석은 해결책이었습니다. phpmyadmin-> sql-> select의 테이블로 이동하면 쿼리 사본을 덤프하고 완료합니다! :)
예, 여기 표에 따라 I / O가 높을 수 있지만 해결 방법이 있습니다.
SELECT *
INTO #temp
FROM table
ALTER TABLE #temp DROP COlUMN column_name
SELECT *
FROM #temp
Thomas의 답변 (+1;))에 동의하지만 원하지 않는 열에 데이터가 거의 없다고 가정 할 때주의해야 할 사항을 추가하고 싶습니다 . 많은 양의 텍스트, xml 또는 이진 얼룩이 포함 된 경우 각 열을 개별적으로 선택하는 데 시간이 걸립니다. 그렇지 않으면 성능이 저하됩니다. 건배!
Mahomedalid가 게시 한 답변에는 작은 문제가 있습니다.
내부 대체 함수 코드가 "" <columns_to_delete>,"을 (를)"로 대체하는 중입니다.이 대체는 마지막 필드에 문자 쉼표 "" "가 없어서 대체 할 필드가 연결 문자열에서 마지막 필드이고 제거되지 않은 경우 문제가 있습니다. 문자열
내 제안 :
SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME),
'<columns_to_delete>', '\'FIELD_REMOVED\'')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<table>'
AND TABLE_SCHEMA = '<database>'), ' FROM <table>');
교체 <table>, <database>그리고`
제거 된 열은 내 경우에 문자열 "FIELD_REMOVED"로 바뀌고 메모리를 안전하게하려고했기 때문에 작동합니다. (내가 제거 한 필드는 약 1MB의 BLOB입니다)
@Mahomedalid 답변을 바탕으로 "mysql에서 일부를 제외한 모든 열 선택"을 지원하기 위해 개선되었습니다.
SET @database = 'database_name';
SET @tablename = 'table_name';
SET @cols2delete = 'col1,col2,col3';
SET @sql = CONCAT(
'SELECT ',
(
SELECT GROUP_CONCAT( IF(FIND_IN_SET(COLUMN_NAME, @cols2delete), NULL, COLUMN_NAME ) )
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tablename AND TABLE_SCHEMA = @database
),
' FROM ',
@tablename);
SELECT @sql;
열이 많은 경우이 SQL을 사용하여 group_concat_max_len을 변경하십시오.
SET @@group_concat_max_len = 2048;
Jan Koritak의 지적 불일치에 대한 해결책이 있습니까?
SELECT CONCAT('SELECT ',
( SELECT GROUP_CONCAT(t.col)
FROM
(
SELECT CASE
WHEN COLUMN_NAME = 'eid' THEN NULL
ELSE COLUMN_NAME
END AS col
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'employee' AND TABLE_SCHEMA = 'test'
) t
WHERE t.col IS NOT NULL) ,
' FROM employee' );
표 :
SELECT table_name,column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'employee' AND TABLE_SCHEMA = 'test'
=================================
table_name column_name
employee eid
employee name_eid
employee sal
=================================
쿼리 결과 :
'SELECT name_eid,sal FROM employee'
항상 같은 열인 경우 열이없는보기를 만들 수 있습니다.
그렇지 않으면, 나는 그렇게 생각하지 않습니다.
SQL을 생성하고 원하는 SQL을 평가하는 경우 SQL을 사용하여 SQL을 생성 할 수 있습니다. 정보 스키마에서 열 이름을 추출 할 때 일반적인 솔루션입니다. 다음은 Unix 명령 행의 예입니다.
대체
- mysql 명령을 사용한 MYSQL
- 테이블 이름이있는 TABLE
- 필드 이름이 제외 된 EXCLUDEDFIELD
echo $(echo 'select concat("select ", group_concat(column_name) , " from TABLE") from information_schema.columns where table_name="TABLE" and column_name != "EXCLUDEDFIELD" group by "t"' | MYSQL | tail -n 1) | MYSQL
실제로 열 이름을 한 번만 추출하여 해당 열을 제외한 열 목록을 구성한 다음 구성한 쿼리를 사용하면됩니다.
그래서 같은 :
column_list=$(echo 'select group_concat(column_name) from information_schema.columns where table_name="TABLE" and column_name != "EXCLUDEDFIELD" group by "t"' | MYSQL | tail -n 1)
이제 $column_list생성 한 쿼리 에서 문자열을 재사용 할 수 있습니다 .
특히 제거 할 열 수가 적은 경우이 문제를 해결하기 위해 다른 관점을 추가하고 싶습니다.
select 문을 생성하기 위해 MySQL Workbench 와 같은 DB 도구를 사용할 수 있으므로 생성 된 문에 대한 해당 열을 수동으로 제거하고 SQL 스크립트에 복사하면됩니다.
MySQL Workbench에서이를 생성하는 방법은 다음과 같습니다.
테이블을 마우스 오른쪽 버튼으로 클릭하고-> SQL 편집기로 보내기-> 모든 문장 선택을 클릭하십시오.
허용되는 답변에는 몇 가지 단점이 있습니다.
- 테이블 또는 열 이름에 백틱이 필요한 경우 실패합니다.
- 생략하려는 열이 목록에서 마지막 인 경우 실패
- 중복되고 불필요한 테이블 이름을 두 번 나열해야합니다 (선택에 대해 한 번, 쿼리 텍스트에 대해 한 번).
- 잠재적으로 열 이름을 잘못된 순서로 반환 할 수 있습니다
이러한 모든 문제는 단순히의 역 따옴표 포함하여 극복 할 수 SEPARATOR당신을위한을 GROUP_CONCAT하고 사용하는 WHERE대신 조건을 REPLACE(). 내 목적을 위해 (그리고 많은 다른 사람들을 상상합니다) 열 이름이 테이블 자체에 나타나는 순서대로 반환되기를 원했습니다. 이를 위해 함수 ORDER BY내부에 명시 적 절 을 사용 GROUP_CONCAT()합니다.
SELECT CONCAT(
'SELECT `',
GROUP_CONCAT(COLUMN_NAME ORDER BY `ORDINAL_POSITION` SEPARATOR '`,`'),
'` FROM `',
`TABLE_SCHEMA`,
'`.`',
TABLE_NAME,
'`;'
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE `TABLE_SCHEMA` = 'my_database'
AND `TABLE_NAME` = 'my_table'
AND `COLUMN_NAME` != 'column_to_omit';
제안이 있지만 해결책은 없습니다. 일부 열에 더 큰 데이터 세트가 있으면 다음을 시도해보십시오.
SELECT *, LEFT(col1, 0) AS col1, LEFT(col2, 0) as col2 FROM table
나는 이것에 대한 답을 낼 때 꽤 늦었습니다. 이것은 내가 항상 그것을하고 솔직하게 최선의 대답보다 100 배 더 좋고 깔끔한 방식이라고 말하면 누군가가 그것을 볼 수 있기를 바랍니다. 그리고 유용하다고 생각하십시오
//create an array, we will call it here.
$here = array();
//create an SQL query in order to get all of the column names
$SQL = "SHOW COLUMNS FROM Table";
//put all of the column names in the array
foreach($conn->query($SQL) as $row) {
$here[] = $row[0];
}
//now search through the array containing the column names for the name of the column, in this case i used the common ID field as an example
$key = array_search('ID', $here);
//now delete the entry
unset($here[$key]);
선택 *는 SQL 반 패턴입니다. 다음과 같은 여러 가지 이유로 프로덕션 코드에서 사용해서는 안됩니다.
처리하는 데 시간이 조금 더 걸립니다. 일이 수백만 번 실행되면 그 작은 비트가 중요 할 수 있습니다. 이 유형의 너절한 코딩으로 인해 속도가 느려지는 느린 데이터베이스는 성능 조정에 가장 어려운 종류입니다.
서버 및 네트워크 병목 현상을 유발하는 것보다 많은 양의 데이터를 전송하고 있음을 의미합니다. 내부 조인이있는 경우 필요한 것보다 많은 데이터를 보낼 가능성은 100 %입니다.
특히 보지 않으려는 새 열을 추가하면 유지 관리 문제가 발생합니다. 또한 새 열이있는 경우 해당 열로 수행 할 작업을 결정하기 위해 인터페이스에 대한 작업을 수행해야 할 수도 있습니다.
뷰를 손상시킬 수 있습니다 (SQl 서버에서 이것이 사실임을 알고 있습니다 .mysql에서는 그렇지 않을 수도 있습니다).
누군가가 다른 순서로 열이있는 테이블을 다시 작성하기에 어리석은 경우 (하지 말아야하지만 시간이 지남에 따라 발생 함) 모든 종류의 코드가 손상 될 수 있습니다. 예를 들어 도시를 지정하지 않고 address_3 필드에 갑자기 넣는 경우와 같이 삽입을 위해 특별히 코드를 작성하면 데이터베이스는 열 순서로만 진행될 수 있습니다. 데이터 유형이 변경되면 충분하지 않지만 스왑 된 열의 데이터 유형이 동일하면 정리하기가 까다로운 잘못된 데이터를 삽입 할 수 있기 때문에 더 나쁩니다. 데이터 무결성에주의해야합니다.
삽입에 사용되는 경우 한 테이블에 새 열이 추가되고 다른 테이블에는 추가되지 않으면 삽입이 중단됩니다.
트리거가 중단 될 수 있습니다. 트리거 문제는 진단하기 어려울 수 있습니다.
열 이름을 추가하는 데 걸리는 시간에 대해이 모든 것을 합산하십시오 (열 이름을 드래그 할 수있는 인터페이스가있을 수도 있습니다 (SQL Server에서하는 것을 알고 있습니다. 이 작업은 mysql 쿼리를 작성하는 데 사용하는 도구입니다.) "유지 보수 문제가 발생할 수 있고 성능 문제가 발생할 수 있으며 데이터 무결성 문제가 발생할 수 있지만 5 분의 개발 시간을 절약했습니다." 원하는 특정 열에
나는 또한 당신 이이 책을 읽을 것을 제안합니다 : http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers-ebook/dp/B00A376BB2/ref=sr_1_1?s=digital-text&ie=UTF8&qid=1389896688&sr=1- 1 & 키워드 = sql + antipatterns
참고 URL : https://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql
'development' 카테고리의 다른 글
| 일반 방법을 숫자 유형으로 제한하는 제약 조건이 있습니까? (0) | 2020.03.02 |
|---|---|
| 복사 제거 및 반환 값 최적화 란 무엇입니까? (0) | 2020.03.02 |
| objectForKey와 valueForKey의 차이점은 무엇입니까? (0) | 2020.03.02 |
| jQuery 객체 검사에서 jQuery 버전 가져 오기 (0) | 2020.03.02 |
| (0 <5 <3)이 왜 true를 반환합니까? (0) | 2020.03.02 |