如何在MySQL加载文件函数中处理带有空格的空值?
下面是查询
SELECT * FROM employees WHERE `date` BETWEEN '2016-12-15' AND '2017-01-04' INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY "" LINES TERMINATED BY ' ';
列中的空值在 csv 中显示为 NULL。我怎样才能把它变成空白?
我不能使用 IFNULL(Col,'')
,因为列是动态获取的,并且编写单个函数来从各种表中提取数据。
if($table == 'employees'){
$this->Target = ClassRegistry::init( $table );
$this->Target->useTable = $table;
$allColumnHeaders = $this->Target->getColumnTypes();
$header_fields = implode( "','", array_keys( $allColumnHeaders ) );
$sql = " Select '" . $header_fields . "' UNION ALL SELECT $fields FROM " . $table . " WHERE date BETWEEN '" . $start_date . "' AND '" . $end_date . "'"
$sql = $sql . " INTO OUTFILE '" . $csvFile . "' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY 'n';";
$result = $this->db->query( $sql );
}
三种方式之一:
IFNULL
IFNULL(Col, '')
合并
COALESCE(Col, '')
案例陈述
CASE WHEN Col IS NULL THEN '' ELSE Col END AS Col