SELECT

Description

Retrieves data from a table.

Syntax

  1. > SELECT
  2.     [ALL | DISTINCT]
  3.     select_expr [[AS] alias], ...
  4.     [INTO variable [, ...]]
  5.     [ FROM table_references
  6.     [WHERE expr]
  7.     [GROUP BY {{col_name | expr | position}, ...
  8.     | extended_grouping_expr}]
  9.     [HAVING expr]
  10.     [ORDER BY {col_name | expr} [ASC | DESC], ...]
  11.     [LIMIT row_count]
  12.     [OFFSET row_count]
  13.     ]

SELECT INTO

SELECT INTO statement enables a query result to be stored in variables or written to a file

  • SELECT … INTO var_list selects column values and stores them into variables.
  • SELECT … INTO OUTFILE writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format.
  1. > SELECT <expr_list> 
  2.   INTO OUTFILE 'file_name' 
  3.     [{FIELDS}
  4.         [TERMINATED BY 'char'] 
  5.         [ENCLOSED BY 'char']
  6.     ]
  7.     [LINES
  8.         [TERMINATED BY 'string']
  9.     ]
  10.     [HEADER 'bool']
  11.     [MAX_FILE_SIZE long] 
  12.     [FORCE_QUOTE {'col1','col2',...}]

Explanations

  • <expr_list> is the query result you want to export.
  • 'file_name' is the file name of the absolute path on the server. The query will fail if a file with the same filename already exists. And the front folder written in the absolute path must be created in advance, otherwise an error will occur.
  • TERMINATED BY is an optional argument as the field separator. The default value is comma ,.
  • ENCLOSED BY is an optional argument as the inclusion character of column fields. The default value is double quotations ".
  • LINES TERMINATED BY is an optional argument as the end of a line. The default value is \n.
  • When HEADER is TRUE, the table’s title will also be exported, vice versa.
  • You can limit the maximum size of the file using MAX_FILE_SIZE in KB. For example, with MAX_FILE_SIZE\=5242880(5GB), tables with size of 10GB are exported as two files distinguished by the ordinal number in their name.
    When this value is not set, one file will be exported by default.
  • FORCE_QUOTE is used to add double quotes for every NOT NULL value in the specified column.
  • NULL values will be exported as \N.

Info

If MAX_FILE_SIZE is not set, a large file may be exported and the operation may fail. Therefore, we recommend you to set this value case by case.

Constraints

  • The query will fail if a file with the same filename already exists.
  • Only .csv types of files are supported currently.
  • Currently, this command supports only the absolute path, and files can be exported only to the server host, not to the remote client.

Examples

  1. > SELECT number FROM numbers(3);
  2. +--------+
  3. | number |
  4. +--------+
  5. |      0 |
  6. |      1 |
  7. |      2 |
  8. +--------+
  10. > SELECT * FROM t1 WHERE spID>2 AND userID <2 || userID >=2 OR userID < 2 LIMIT 3;
  12. > SELECT userID,MAX(score) max_score FROM t1 WHERE userID <2 || userID > 3 GROUP BY userID ORDER BY max_score;
  1. select * from t1 into outfile '/Users/tmp/test.csv' 
  2. FIELDS TERMINATED BY ',' 
  3. ENCLOSED BY '"'
  4. LINES TERMINATED BY '\n' 
  5. header 'TRUE' 
  6. MAX_FILE_SIZE 9223372036854775807 
  7. FORCE_QUOTE (a, b)