Home Database Mysql Tutorial mysql tutorial: Backup using SELECT INTO

mysql tutorial: Backup using SELECT INTO

Dec 16, 2016 pm 04:00 PM

Use SELECT INTO for backup
Use SELECT INTO for backup is very similar to MYSQLDUMP. It also backs up the database to a specified file. The result file can only be created on the MySQL server, not any other host. The syntax format of the SELECT INTO statement is as follows:
Select … into outfile 'path_and_filename' ;
Example:
Use the SELECT INTO statement to query the "mr_gly" table in the database "mr_mysql" and back up the table to "D:\gly.txt" directory, the name of the file is "gly.txt".
mysql> use mr_mysql
Database changed
mysql> select * from mr_gly into outfile "D:\gly.txt";
Query OK, 5 rows affected (0.00 sec)
The following parameters are non-default parameters for the SELECT INTO statement .​​​​​Specifies that only character fields are included
[escaped by '\'] ]
[lines terminated by 'n' ] //Set what character should be used to replace long line breaks
The following are several examples of non-default parameters of the SELECT INTO statement. .
Example:
Between each field, the default tab character is replaced by the character "|".
mysql> use tpsc
Database changed
mysql> select * from jtsr into outfile "D:\user1.txt" fields terminated by '|' ;
Query OK, 5 rows affected (0.00 sec)
Example:
Enclosed keyword Surround all fields with the specified character "double quotes".
mysql> select * from jtsr into outfile "D:\user2.txt" fields terminated by '|' enclosed by '"';
Query OK, 5 rows affected (0.02 sec)
Example:
Use of Optionally keyword, As a result, only character fields are enclosed in double quotes.
mysql> select * from jtsr into outfile "D:\user3.txt" fields terminated by '|' optionally enclosed by '"' ;
Query OK, 5 rows affected (0.02 sec )
Example: The use of
lines terminated, so that the break between each line is replaced by the character "n".
mysql> select * from jtsr into outfile "D:\user4.txt" fields terminated by '|' lines terminated by 'n' ;
Query OK, 5 rows affected (0.02 sec)
Example:
Use these parameters comprehensively.
mysql> select * from jtsr into outfile "D:\user5.txt" fields terminated by '|' optionally enclosed
by ''' lines terminated by 'n' ;
Query OK, 5 rows affected (0.02 sec)
Example :
Use the conditions in the SELECT statement for backup.
mysql> select * from jtsr where id>3 into outfile "D:\user6.txt" fields terminated by '|' optionall
y enclosed by '"' lines terminated by ' n' ;
Query OK, 2 rows affected (0.01 sec)
Note: When using the SELECT INTO statement, do not overwrite the existing file when naming the backup file; do not forget to use replacement when writing the location of the file output Code character "".

The above is the mysql tutorial: using SELECT INTO for backup. For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!


Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to backup Google Chrome extensions How to backup Google Chrome extensions Jan 30, 2024 pm 12:36 PM

How to backup Google Chrome extension? For most Google Chrome users, more or less plug-ins are installed during daily use. The existence of plug-ins can improve our usage experience. When we reinstall the system or browser, these plug-ins cannot be retained, and it is troublesome to download and install them again. So is there a way to back up the currently installed plug-ins? Here’s how to do it. The tutorial method of backing up chrome plug-ins first opens Google Chrome, click the menu in the upper right corner, and select More Tools - Extensions. Click Package extension above the extensions page. In C:UsersAdministratorAppDataLocalGoogleChromeUserDataDe

How to delete startup backup in Windows 11's File Explorer How to delete startup backup in Windows 11's File Explorer Feb 18, 2024 pm 05:40 PM

If you wish to hide the "Start Backup" option in Windows 11's File Explorer, here's what you can do. There are several ways to disable or hide the startup backup option in File Explorer, and we'll briefly list some methods to help you accomplish this task quickly. Before you get started, you need to understand that this option is closely tied to OneDrive. Once you open a library folder (such as Document, Pictures, Music, etc.), it will immediately appear in the file explorer's path. How to delete startup backup in Windows 11’s File Explorer To delete startup backup in Windows 11’s File Explorer, follow the steps below

How to restore the deleted hosts file How to restore the deleted hosts file Feb 22, 2024 pm 10:48 PM

Title: How to restore the hosts file after deletion Summary: The hosts file is a very important file in the operating system and is used to map domain names to IP addresses. If you accidentally delete the hosts file, you may be unable to access certain websites or have other network problems. This article will introduce how to recover accidentally deleted hosts file in Windows and Mac operating systems. Text: 1. Restore hosts file in Windows operating system. Hosts file in Windows operating system

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to backup system with ghost-ghost backup tutorial How to backup system with ghost-ghost backup tutorial Mar 06, 2024 pm 04:30 PM

Recently, many friends have asked the editor how to back up the system with ghost. Next, let us learn the tutorial on how to back up the system with ghost. I hope it can help everyone. 1. After running Ghost, click "OK", as shown in the figure. 2. Click "Local" → "Partition" → "ToImage" (meaning: local → partition → to image file), as shown in the figure. 3. The Select Local Hard Disk window appears, click the hard disk where the partition to be backed up is located, and then click "OK", as shown in the figure. 4. The Select Source Partition window appears (the source partition is the partition you want to back up), click on the partition where the system is located (usually Zone 1, be sure to get it right), and then click "OK", as shown in the figure. 5. Play at this time

How to install, uninstall, and reset Windows server backup How to install, uninstall, and reset Windows server backup Mar 06, 2024 am 10:37 AM

WindowsServerBackup is a function that comes with the WindowsServer operating system, designed to help users protect important data and system configurations, and provide complete backup and recovery solutions for small, medium and enterprise-level enterprises. Only users running Server2022 and higher can use this feature. In this article, we will explain how to install, uninstall or reset WindowsServerBackup. How to Reset Windows Server Backup If you are experiencing problems with your server backup, the backup is taking too long, or you are unable to access stored files, then you may consider resetting your Windows Server backup settings. To reset Windows

How to perform system backup and restore on Kirin OS? How to perform system backup and restore on Kirin OS? Aug 07, 2023 pm 02:22 PM

How to perform system backup and restore on Kirin OS? Kirin operating system is an open source operating system independently developed in China and is widely used in various scenarios. System backup and recovery is a very important task when using Kirin operating system. Backup systems can prevent data loss due to malfunctions or misoperations, while system recovery can quickly restore normal functionality in the event of a system crash. This article will introduce in detail how to perform system backup and recovery on Kirin operating system, and attach relevant code examples. Backing Up the System In order to backup the entire system we can

GitLab's code base backup and recovery functions and implementation steps GitLab's code base backup and recovery functions and implementation steps Oct 20, 2023 pm 12:04 PM

GitLab is an open source code hosting platform that provides rich features, including code base backup and recovery. Code base backup is one of the important steps to ensure the security of the code and it can help us recover the data when unexpected things happen. This article will introduce GitLab's code base backup and recovery functions, and provide corresponding implementation steps and code examples. GitLab's code base backup function GitLab provides two types of backup: incremental backup and full backup. Incremental backup: Incremental backup means backing up only the latest changed data

See all articles