In MySQL, there is a provision to search based on natural mode. Let us consider the following example to understand this in detail. Creation of the table: CREATE TABLE ‘PROP_TBL’ ( ‘PROPERTY’ VARCHAR(100), ‘DOMAIN’ VARCHAR(20), ‘VALUE’ VARCHAR(2000), ‘PATH’ VARCHAR(100), PRIMARY KEY (‘PROPERTY’, ‘DOMAIN’, ‘PATH’), FULLTEXT (PROPERTY,DOMAIN))ENGINE=InnoDB; And use the following
Let us consider a table representing employee data with a few columns, and, of course, their salary details as well. It is not wise to expose this table to all querying entities since there is sensitive data that makes sharing the contents problematic. In real time, there are VIEWS that
MySQL has various ways to search for or lookup a given string. One such powerful mechanism is by using FIND_IN_SET. This function enables a lookup for a given string in a set (probably comma separated ones). Example: CREATE TABLE ‘CITIES’ (‘ID’ INT(11) NOT NULL AUTO_INCREMENT,’CITY’ VARCHAR(25) NOT NULL,’COUNTRY’ VARCHAR(25) NOT
We know that AUTO_INCREMENT is used to have a sequential value auto incremented by itself for the records that we insert. CREATE TABLE AUTO_TABLE (ID INT NOT NULL AUTO_INCREMENT,PRIMARY KEY (ID),NAME VARCHAR (30)); Here we see that when the records are added, the values in the ID column will start
Amid tons of data, finding a particular string’s presence in the data is extremely tedious. MySQL has a command named LOCATE that can be used with certain conditions and the yield is truly useful. Using the LOCATE Command LOCATE (“”, column_name, ) For example, if we are trying to look
MySQL provides you a mechanism to find the current user. SELECT USER(), CURRENT_USER(); This command comes handy when you have associated a proxy privilege to a user. Sample: mysql SELECT USER(), CURRENT_USER();+————————+————————-+| USER() | CURRENT_USER() |+————————+————————-+| [email protected] | [email protected] |+————————+————————-+
As with any database, MySQL provides powerful user management feature. Learn how to remove a user from the database. DROP USER SRIDHAR However, the catch here is that the user will be dropped immediately, but the current active sessions will be allowed and closed only after the user exits. To
MySQL provides a REPEAT command that can be used when you want to repeat a particular string, a defined number of times. Consider the example below: SELECT CONCAT(‘S’,REPEAT(“E”,2)) Here, we can observe that the output is the text ‘SEE’. The letter ‘E’ has been repeated two times as indicated in
In MySQL, once we use the COMPRESS method to compress a particular text, the LENGTH of the resultant text is not the same as that of the original text. However, using UNCOMPRESSED_LENGTH method on the compressed text results in the exact length of the original text, even though the text