MySQL

Understanding FULLTEXT Searches

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

Adjusting Views to Reduce Visibility of Data in MySQL

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

Using Find_in_Set in MySQL

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

Using AUTO_INCREMENT in MySQL

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

Using the Locate Command in MySQL

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

Finding the Current User in MySQL

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] |+————————+————————-+

Employing DROP USER in MySQL

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

Using REPEAT in MySQL

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

Understanding UNCOMPRESSED_LENGTH in MySQL

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

No more posts to show