MySQL té una funció per exportar una taula al fitxer CSV. Un format de fitxer CSV és un valor separat per comes que fem servir per intercanviar dades entre diverses aplicacions com ara Microsoft Excel , Goole Docs i Open Office. És útil tenir dades MySQL en format de fitxer CSV que ens permeti analitzar-les i formatar-les de la manera que vulguem. És un fitxer de text sense format que ens ajuda a exportar dades molt fàcilment.
MySQL proporciona una manera senzilla d'exportar qualsevol taula a fitxers CSV que resideix al servidor de bases de dades. Hem d'assegurar-nos de les coses següents abans d'exportar dades de MySQL:
- El procés del servidor MySQL té accés de lectura/escriptura a la carpeta especificada (destinació), que conté el fitxer CSV.
- El fitxer CSV especificat no hauria d'existir al sistema.
Per exportar la taula a un fitxer CSV, farem servir el SELECCIONA A....FIXER declaració. Aquesta afirmació és un compliment de la CARREGA DADES comanda, que s'utilitza per escriure dades d'una taula i després exportar-les a un format de fitxer especificat a l'amfitrió del servidor. És per assegurar-nos que tenim un privilegi de fitxer per utilitzar aquesta sintaxi.
SELECT column_lists INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
També podem utilitzar aquesta sintaxi amb una declaració de valors per exportar dades directament a un fitxer. La següent afirmació ho explica més clarament:
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Si volem exportar totes les columnes de la taula , utilitzarem la sintaxi següent. Amb aquesta declaració, l'ordenació i el nombre de files serà controlat per la DEMANAT PER i LÍMIT clàusula.
java substitueix el caràcter de la cadena
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
De l'anterior,
LÍNIES TERMINADAS PER ',' : S'utilitza per indicar les línies de files d'un fitxer que finalitzen amb un operador de coma. Cada línia conté les dades de cada columna al fitxer.
CAMPS TANCATS PER ''' : S'utilitza per especificar el camp del fitxer tancat entre cometes dobles. Impedeix els valors que contenen separadors de comes. Si els valors continguts entre cometes dobles marquen, no reconeix la coma com a separador.
Ubicació d'emmagatzematge del fitxer exportat
La ubicació d'emmagatzematge de cada fitxer exportat a MySQL s'emmagatzema a la variable predeterminada fitxer_segur_priv . Podem executar l'ordre següent per obtenir la ruta per defecte d'un fitxer exportat.
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Després de l'execució, donarà el resultat de la següent manera on podem veure aquest camí: C:/ProgramData/MySQL/MySQL Server 8.0/Càrregues/ com a ubicació del fitxer per defecte. Aquest camí s'utilitzarà en el moment d'executar una ordre d'exportació.
Si volem canviar la ubicació d'exportació per defecte del fitxer CSV especificat al fitxer fitxer_segur_priv variable, hem d'editar la variable my.ini fitxer de configuració. A la plataforma Windows, aquest fitxer es troba en aquest camí: C:ProgramDataMySQLMySQL Server X.Y .
Si volem exportar dades de MySQL, primer hem de crear un base de dades amb almenys un taula . Utilitzarem aquesta taula com a exemple.
1 a 100 roman núm
Podem crear un base de dades i taula executant el codi següent als editors que estem utilitzant:
CREATE DATABASE testdb; USE testdb; CREATE TABLE employee_detail ( ID int NOT NULL AUTO_INCREMENT, Name varchar(45) DEFAULT NULL, Email varchar(45) DEFAULT NULL, Phone varchar(15) DEFAULT NULL, City varchar(25) DEFAULT NULL, PRIMARY KEY (ID), UNIQUE KEY unique_email (Email), UNIQUE KEY index_name_phone (Name,Phone) ) INSERT INTO employee_detail ( Id, Name, Email, Phone, City) VALUES (1, 'Peter', '[email protected]', '49562959223', 'Texas'), (2, 'Suzi', '[email protected]', '70679834522', 'California'), (3, 'Joseph', '[email protected]', '09896765374', 'Alaska'), (4, 'Alex', '[email protected]', '97335737548', 'Los Angeles'), (5, 'Mark', '[email protected]', '78765645643', 'Washington'), (6, 'Stephen', '[email protected]', '986345793248', 'New York');
Si executem el SELECCIONA declaració, veurem la següent sortida:
Exporteu dades de MySQL en format CSV mitjançant la instrucció SELECT INTO ... OUTFILE
Per exportar les dades de la taula a un fitxer CSV, hem d'executar la consulta de la següent manera:
SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Obtindrem la següent sortida, on podem veure que sis files estan afectades. És perquè la taula especificada només conté sis files.
np std
Si tornem a executar la mateixa instrucció, MySQL genera un missatge d'error que es pot veure a la sortida següent:
El missatge d'error ens indica que el nom de fitxer especificat ja existeix a la ubicació especificada. Així, si exportem el nou fitxer CSV amb el mateix nom i ubicació, no es podrà crear. Podem resoldre això o bé suprimir el fitxer existent a la ubicació especificada o canviar el nom del fitxer per crear-lo al mateix lloc.
Podem verificar el fitxer CSV creat a la ubicació especificada o no navegant a una ruta determinada de la següent manera:
Quan obrim aquest fitxer, es veurà com la imatge següent:
A la imatge, podem veure que els camps numèrics estan entre cometes. Podem canviar aquest estil afegint OPCIONALMENT clàusula abans de ENCLOSED BY :
SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Exportació de dades amb l'encapçalament de la columna
De vegades volem exportar dades juntament amb encapçalaments de columnes que fan que el fitxer sigui convenient. El fitxer exportat és més comprensible si la primera línia del fitxer CSV conté els encapçalaments de les columnes. Podem afegir els encapçalaments de les columnes utilitzant el UNIÓ TOTS declaració de la següent manera:
SELECT 'Id', 'Name', 'Email', 'Phone', 'City' UNION ALL SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS TERMINATED BY ';' ENCLOSED BY ''' ESCAPED BY ''' LINES TERMINATED BY ' ';
En aquesta consulta, podem veure que hem afegit un encapçalament per a cada nom de columna. Podem verificar la sortida navegant a l'URL especificat on la primera línia conté l'encapçalament de cada columna:
Exporta la taula MySQL en format CSV
MySQL OUTFILE també ens permet exportar la taula sense especificar cap nom de columna. Podem utilitzar la sintaxi següent per exportar la taula en un format de fitxer CSV:
TABLE employee_detail ORDER BY City LIMIT 1000 INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Si executem la instrucció anterior, la nostra eina de línia d'ordres produeix el resultat següent. Significa que la taula especificada conté sis files, que s'han exportat employee_backup.csv dossier.
Maneig de valors nuls
De vegades, els camps del conjunt de resultats tenen valors NULL, aleshores el fitxer de destinació (tipus de fitxer exportat) contindrà N en comptes de NULL. Podem solucionar aquest problema substituint el valor NULL per 'no aplicable (N/A)' utilitzant el IFNULL funció. La declaració següent ho explica més clarament:
SELECT Name, Email, Phone, IFNULL(Phone, 'N/A') FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Exporta la taula en format CSV amb MySQL Workbench
Si no volem accedir al servidor de bases de dades per exportar el fitxer CSV, MySQL ofereix una altra manera, és a dir, utilitzant MySQL Workbench. Workbench és una eina GUI per treballar amb la base de dades MySQL sense utilitzar una eina de línia d'ordres. Ens permet exportar el conjunt de resultats d'una instrucció a un format CSV al nostre sistema local. Per fer-ho, hem de seguir els passos següents:
- Executeu la instrucció/consulta i obteniu el seu conjunt de resultats.
- A continuació, al tauler de resultats, feu clic 'exporta el conjunt de registres a un fitxer extern' opció. El conjunt de registres s'utilitza per al conjunt de resultats.
- Finalment, es mostrarà un nou quadre de diàleg. Aquí, hem de proporcionar un nom de fitxer i el seu format. Després d'omplir el detall, feu clic a Desa botó. La següent imatge ho explica més clarament:
Ara, podem verificar el resultat navegant al camí especificat.
np.argmax