MYSQL 命令获取列标题、位置和排序依据

Question

所以我想从数据库表导出到 .csv 文件以供使用，但我的问题是正确获取所有以下参数。

1. First, I need to export all the column headers as the first column in the csv file,
2. then from there I need to export all the data that I need using (WHERE ...) and
3. finally I need to order a certain column (ORDER BY ...).

目前，我只能得到 3 个参数中的两个正确。所以我目前的声明是:

SELECT * 
FROM ( " ... all my rows names" 
       UNION ALL 
       ( SELECT * 
         from table_name 
         where Set_Type = "..." or ID = "..." ORDER by probability 
       )
     ) as tbl INTO OUTFILE '   ' FIELDS TERMINATED by ',';

目前，该命令将列放入第一行，并且 WHERE Set_Type 或 ID 部分正确。但是，它不会对概率部分进行排序。有没有办法可以在导出之前完成所有操作？最好在一个命令中，但如果是多个命令，我所做的每次都需要特定的 ID，因此命令会有所不同。

Answer 1

以下链接有很好的解释:

How to save a MySql query with headers

但是为了让您不必访问该网站，他们给出的示例是:

To include the column names (so that the .CSV file contains headers on the first line), you can hardcode them in another SELECT statement, prepended to the rest of the query by a UNION ALL operator.

/* Add column headers */
SELECT 'OrderId','CustomerID','EmployeeID','OrderDate','RequiredDate','ShippedDate','ShipVia','Freight','ShipName','ShipAddress','ShipCity','ShipRegion','ShipPostalCode','ShipCountry','OrderID','ProductId','UnitPrice','Quantity','Discount'

UNION ALL

/* Now the actual query */
SELECT o.OrderId, o.CustomerID, o.EmployeeID, o.OrderDate, o.RequiredDate, o.ShippedDate, o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, o.ShipCity, o.ShipRegion, o.ShipPostalCode, o.ShipCountry, od.OrderID, od.ProductId, od.UnitPrice, od.Quantity, od.Discount 

FROM `Orders` o  LEFT JOIN `Order Details` od ON od.OrderID = o.OrderID 

/* Save the query results to a file */
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

基本上，单独选择 header 并使用 UNION ALL。只需将 Order by 添加到实际获取数据的查询部分即可。

示例

所以这个例子对我有用。您在底部获得 header 的原因是因为您将 ORDER BY 子句应用于整个 UNION。您需要将 UNION 的第二部分括在括号中，以便仅将 ORDER BY 应用于括号内的所有内容。

这对我有用:

SELECT 'ID', 'Name', 'Description'

UNION ALL 

(SELECT id, name, description

FROM test.questions

ORDER BY name Asc

LIMIT 9999);

来自 MYSQL 站点的更多信息:

However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.

基本上，在应用 ORDER BY 时还必须应用 LIMIT，否则它不会生效。