MySQL Articles - Page 332 of 355

It can be done with the help of LOAD DATA INFILE statement. To illustrate the concept we are having the following data, separated by tab, in ‘A.txt’ whose path is d:/A.txt −100 John  USA 10000 101 Paul  UK  12000 102 Henry NZ  11000 103 Rick  USA 17000 104 Corey USA 15000We want to load the data of A.txt into the following table named employee_tbl −mysql> Create table employee_tbl(Id Int, Name varchar(20), Country Varchar(20), Salary Int); Query OK, 0 rows affected (0.91 sec)Now, the transfer of data from a file to a database table can be done with the help ... Read More

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 statements that can be used to import or export data between MySQL and data files −LOAD DATA INFILEThis statement is used for importing the data from data files into our database. It reads data records directly from a file and inserts them into a table. Its syntax would be as follows −SyntaxLOAD DATA LOCAL INFILE '[path/][file_name]' INTO TABLE [table_name ];Here, the path is the address of the file.file_name is the name ... Read More

MySQL will return NULL as the output if any of the argument of CONV() function is NULL or if the value provided for the base is out of limit(i.e. not between minimum 2 and maximum 36). Following examples would demonstrate it. Example mysql> Select CONV(10,NULL,2); +-----------------+ | CONV(10, NULL,2)| +-----------------+ | NULL | +-----------------+ 1 row in set (0.00 sec) mysql> Select CONV(10,10, NULL); +------------------+ | CONV(10,10, NULL)| +------------------+ | NULL | +------------------+ 1 row in set (0.00 sec) mysql> Select CONV(NULL,10,2); +-----------------+ | CONV(null,10,2) | +-----------------+ | NULL | +-----------------+ 1 row in set (0.00 sec)

As we know that by default searching in LOCATE() function starts from beginning. We can manage the start position by giving an argument to specify the position from which we want to start the search in string. Following example will demonstrate it −Examplemysql> Select LOCATE('good','Ram is a good boy. Is Ram a good boy?',11)As Result; +--------+ | Result | +--------+ |     29 | +--------+ 1 row in set (0.00 sec)In the above example, we have given the value 11 as the argument for position. It means that MySQL will start searching from 11th position.

In case if the substring is there for more than one time in the string then MySQL LOCATE() function will return the position of the first occurrence of the substring.Examplemysql> Select LOCATE('good','Ram is a good boy. Is Ram a good boy?')As Result; +--------+ | Result | +--------+ |     10 | +--------+ 1 row in set (0.00 sec)As we can see that the substring ‘good’ is in the string for two times. The first occurrence is at position 10 and another occurrence is at position 29. MySQL returns the position of the first occurrence.

As we know that certain objects within MySQL are known as identifiers. These objects include a database, table, index, column, alias, view, stored procedure, partition, tablespace etc. Identifiers are stored using Unicode (UTF-8). The maximum length of each type of identifier is given in the following table:Sr. No.IdentifierMaximum Length (characters)1Database642Table643Column644Index645Constraint646Stored Procedure or Function647Trigger648View649Event6410Tablespace6411Log File Group6412Alias25613Compound Statement Label16

We must have to use quotes with reserved words to use them as an identifier. The quotes can be single or double depends upon ANSI_QUOTES SQL mode.If this mode is disabled then the identifier quote character is the backtick (“`”). Consider the following example in which we created a table named ‘select’ −mysql> create table `select`(id int); Query OK, 0 rows affected (0.19 sec)If this mode is enabled then we can use backtick (“`”) and double quotes (“”) both as identifier quote character. Consider the following example in which we created a table named ‘trigger’ −mysql> Create table "trigger" (id ... Read More

In MySQL, it is not possible that a client can hold user variable permanently. It is because all the variables for a given client connection are automatically freed when that client exits.

In MySQL, a user-defined variable defined by one client cannot be seen or used by another client because user-defined variables are connection-specific. It means that all variables for a given client connection are automatically freed when that client exits

MySQL allows us to use integer values as the arguments of the LOCATE() function. We do not need to use quotes. It can be demonstrated with the help of the following example −Examplemysql> Select LOCATE(5,1698235); +-------------------+ | LOCATE(5,1698235) | +-------------------+ |                 7 | +-------------------+ 1 row in set (0.00 sec) mysql> Select LOCATE(56,1698235); +--------------------+ | LOCATE(56,1698235) | +--------------------+ |                  0 | +--------------------+ 1 row in set (0.00 sec) mysql> Select LOCATE(23,1698235); +--------------------+ | LOCATE(23,1698235) | +--------------------+ |                  5 | +--------------------+ 1 row in set (0.00 sec)