🚀

9

CSV (Comma Separated Values - , ) . CSV .

Merion Academy


   CSV  MySQL

, MySQL CSV CSV MySQL.


MySQL CSV

:

  1. root sudo
  2. MySQL root
  3. phpMyAdmin ()

MySQL CSV phpMyAdmin

phpMyAdmin MySQL. CSV.

  1. phpMyAdmin.
  2. Databases ( ) . Databases
  3. , . user. user
  4. . , .
  5. Export .
  6. . Format, CSV, Go. Export
  7. , CSV.  CSV

MySQL CSV

CLI, , .

MySQL, , .

:

SELECT * FROM myTable
INTO OUTFILE '\tmp\myExportFile.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';	

myTable . \tmp\myExportFile.csv . .csv .

. Linux. Windows, c:/folder/file.csv .
MySQL

:

SELECT column1, column2, column3, column4
FROM myTable
WHERE column2 = 'value';

column1 ( ) , . FROM, , . WHERE , . , . :

SELECT order_date, order_number, order_status
FROM current_orders
WHERE order_status='pending';
< (timestamp) CSV-

CSV :

SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = '\tmp';
SET @PREFIX = 'myTable';
SET @EXT    = '.csv';
SET @CMD = CONCAT("SELECT * FROM myTable INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
"' FIELDS ENCLOSED BY '\"'
 TERMINATED BY ';'
ESCAPED BY '\"'"," 
LINES TERMINATED BY '\r\n';");
PREPARE statement FROM @CMD;
EXECUTE statement;	

, myTable .

: SELECT * FROM . , .

UNION :

(SELECT 'column1','column2','column3','column4')
UNION
(SELECT column1, column2, column3, column4
FROM myTable
INTO OUTFILE '\tmp\myExportFile.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n')	

() , N. NULL , :

SELECT column1, column2, IFNULL(column3, 'N/A')
FROM myTable INTO OUTFILE '\tmp\myExportFile.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"' LINES
TERMINATED BY '\r\n');	

IFNULL column3. , N/A.


MySQL CSV mysqldump

mysqldump MySQL CSV. :

mysqldump tab=/var/lib/mysqlfiles/ fieldsenclosedby='"' fieldsterminatedby=',' linesterminatedby='\n' myTable

myTable /var/lib/mysql-files.

. , CSV. , , MySQL .

MySQL CSV CSV Engine

CSV Engine MySQL . , auto_increment. :

ALTER TABLE myTable ENGINE=CSV;	

CSV. CSV .

, CSV Engine mysqldump .

CSV MySQL

:

  1. CSV-, ,
  2. MySQL FILE INSERT
  3. phpMyAdmin ()

CSV

1: MySQL

MySQL, :

mysql u username p	

username . MySQL. MySQL.

mysql u username p

2. MySQL CSV

MySQL CSV, . , CSV, 3 .

, :

USE database_name;	

database_name , . :

CREATE TABLE table_name (
            id INT NOT NULL AUTO_INCREMENT,
            column_1 VARCHAR(255) NOT NULL,
            column_2 DATE NOT NULL,
            column_3 DECIMAL(10 , 2 ) NULL,
            column_4 INTEGER,
            PRIMARY KEY (id)
);	

table_name , . column_n CSV-, .

  • column_1 .
  • column_2 .
  • column_3 . .
  • column_4 .

, , , . MySQL , .

3: CSV MySQL Table

CSV MySQL, :

LOAD DATA INFILE '/home/export_file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '/n'
IGNORE 1 ROWS;

, , , :

  • LOAD DATA INFILE - CSV . ( ), CSV-. CSV , LOAD DATA LOCAL INFILE.
  • INTO TABLE - , CSV. table_name .
  • FIELDS TERMINATED BY , , . , .
  • ENCLOSED BY - , " .
  • LINES TERMINATED BY - , .
  • IGNORE 1 ROWS; - CSV- . MySQL , . MySQL.

CSV phpMyAdmin

phpMyAdmin, CSV.

  1. cPanel phpMyAdmin. phpMyAdmin
  2. , , CSV-.
  3. , Import. Import
  4. Choose File ( ) CSV.
  5. Format, CSV, , , . Go
  6. Go CSV.

, CSV- MySQL CSV- MySQL phpMyAdmin.


>