Transferring the information between MySQL and data files mean importing data from data files into our database or exporting data from our database into files. MySQL is having two commands that can be used to import or export data between MySQL and data files through the command line −mysqlimport Actually, mysqlimport command reads a spread of data formats, including comma-and tab-delimited, and inserts the information into a database. In other words, we can say that it provides a command-line interface for importing the data i.e. command-line interface to the LOAD DATA INFILE statement. Its syntax would be as follows −SyntaxMysqlimport [options] ... Read More

MySQL throws this error because of the two reasons, either no directory is specified under --secure--file--priv variable or we are giving the wrong path in our query while importing or exporting the data. To tackle this error we must have to check the value of –secure—file—priv variable by following query −mysql> Select @@global.secure_file_priv; +---------------------------+ | @@global.secure_file_priv | +---------------------------+ | C:\mysql\bin\mysql-files\ | +---------------------------+ 1 row in set (0.00 sec)We can see there is path under secure_file_priv variable and all the files would be created under this directory when we export the data.But, if the above command shows NULL as result then ... Read More

If we want to store any other value than \N in CSV file on exporting the data to CSV file from a table which contains NULL value(s) then we need to replace \N values with other value by using IFNULL statement. To illustrate it we are taking the following example −ExampleSuppose if we want to export the values of the table ‘student_info’ having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101  | YashPal | Amritsar   | History    | | 105  | ... Read More

If we export the data from a table having NULL values then MySQL will store \N in CSV file for the record MySQL table having NULL values. It can be illustrated with the help of the following example −ExampleSuppose if we want to export the values of the table ‘student_info’ having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101  | YashPal | Amritsar   | History    | | 105  | Gaurav  | Chandigarh | Literature | | 125  | Raman   | ... Read More

For adding the column values we need to use UNION statement. It can be demonstrated with the help of the following example −ExampleIn this example data from student_info will be exporting to CSV file. The CSV file will have the first line as the name of the columns.mysql>(SELECT 'id', 'Name', 'Address', 'Subject')UNION(SELECT id, Name, Address, Subject From student_info INTO OUTFILE 'C:/mysql/bin/mysql-files/student_25.CSV' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r'); Query OK, 7 rows affected (0.04 sec)After executing the above query MySQL creates Student_25.CSV file which have the following values −id;    "Name";     ... Read More

Sometimes we need to export data into a CSV file whose name has a timestamp at which that file is created. It can be done with the help of MySQL prepared statement. To illustrate it we are using the following example −ExampleThe queries in the following example will export the data from table ‘student_info’ to the CSV file having a timestamp in its name.mysql> SET @time_stamp = DATE_FORMAT(NOW(), '_%Y_%m_%d_%H_%i_%s'); Query OK, 0 rows affected (0.00 sec) mysql> SET @FOLDER = 'C:/mysql/bin/mysql-files'; Query OK, 0 rows affected (0.00 sec) mysql> SET @FOLDER = 'C:/mysql/bin/mysql-files/'; Query OK, 0 rows affected ... Read More

MySQL SUM() function will return 0, rather than NULL, along with a warning on getting the character type column as its argument. Following example using data from table named ‘Social’ will illustrate it −Examplemysql> Select * from Social; +------+-------+ | Id   | Name  | +------+-------+ | 100  | Rahul | +------+-------+ 1 row in set (0.00 sec) mysql> Select SUM(Name) From Social; +-----------+ | SUM(Name) | +-----------+ | 0         | +-----------+ 1 row in set, 1 warning (0.00 sec)

When MySQL SUM() function got a column, having no values, an argument then it will return NULL, rather than 0, as output. But if we want to customize this output to show 0 as output then we can use MySQL COALESCE() function which accepts two arguments and returns the second argument if the first argument is NULL, otherwise, it returns the first argument. To illustrate it, we are taking the example of ‘Tender’ table having the following data −mysql> Select * from tender; +----+---------------+--------------+ | Sr | CompanyName   | Tender_value | +----+---------------+--------------+ | 1  | Abc Corp.   ... Read More

When MySQL SUM() function got a column, having no values, as an argument then it will return NULL, rather than 0, as output. The column can be of any data type. Following the example, using a table named ‘social’ having only one column named ‘id’ with no values, will illustrate itExamplemysql> Describe Social; +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Id    | int(11)     | YES  |     |   NULL  |       | | Name  | varchar(20) | YES  |     ... Read More

It can be done by applying TRIM() function on the column along with MySQL UPDATE statement. The example below will make it more clear.ExampleSuppose, we have a table ‘Employee’ which have the prefix ‘Dept.’ with all the values of Column ‘Department’ as follows −mysql> Select * from Employee; +------+----------------+------------+----------------------+ | Id   | Name           | Address    | Department           | +------+----------------+------------+----------------------+ | 100  | Raman          | Delhi      | IT Dept.             | | 101  | Mohan       ... Read More