Use powerful regular expression function REGEXP instead of LIKE in MySQL

1) Result include start with in column values

SELECT * FROM database.table_name WHERE column_name REGEXP '^[abcd]';

2) Result include start with range in column values

SELECT * FROM database.table_name WHERE column_name REGEXP '^[a-c]';

3) Result excluded start with in column values

SELECT * FROM database.table_name WHERE column_name REGEXP '^[^abcd]'

4) Result Conatins ban in column values

SELECT * FROM database.table_name WHERE column_name REGEXP 'ban+';

5) Result contains all character in column values

SELECT * FROM database.table_name  WHERE column_name REGEXP 'shr?';

6) Result contains 1and next any number/character in column values

SELECT * FROM database.table_name WHERE column_name  REGEXP '1.';

7) Result end with word in column values

SELECT column_name FROM database.table_name WHERE column_name  REGEXP 'ban$';

8) Result contains word in column values

 SELECT column_name FROM database.table_name WHERE  column_name REGEXP 'ban';

9) Result Strings that contain a particular substring at a specific position

 SELECT column_name FROM database.table_name WHERE column_name  REGEXP '^....ban';//

Partitioning in MySQL

Add partition in a table

ALTER TABLE database.table_name PARTITION BY Partition_Type(Column_Name) (
PARTITION Partition_Name VALUES IN (qualify_value),
PARTITION Partition_Name VALUES IN (qualify_value)
 );

Get all detail of partitions in a database 


SELECT * FROM information_schema.partitions WHERE TABLE_SCHEMA='database'  AND PARTITION_NAME IS NOT NULL;


Select the existing record from partitioned table in MySQL 


a) SELECT * FROM database.table_name PARTITION (Partition_Name1 );
b) SELECT * FROM database.table_name PARTITION (Partition_Name1 ,Partition_Name2 );
c) SELECT * FROM database.table_name PARTITION (Partition_Name1 ,Partition_Name2 ) WHERE qualify_value='value';

Get dump of MySQL table data

MySQL dump of only single table record  data 


mysqldump -u root -PPORT_NUMBER -ppassword  --complete-insert database_name table_name > file_name.sql

MySQL dump of only multiple table record  data 


mysqldump -u root -PPORT_NUMBER -ppassword  --complete-insert database_name table_name1 table_name2 table_name3...  > file_name.sql

MySQL dump of only selected record 

mysqldump -u root -PPORT_NUMBER -ppassword database_name table_name --where="condition"

Example.

1)  mysqldump -t -u root -P3306 -ppassword school student --where="studentId=123"

                                                                   OR

   mysqldump -t -u root -ppassword school student --where="studentId=123" (without port)

2)  mysqldump -t -u root -ppassword school student --where="studentId=123 AND Name Like %sant%"

Get table size (MB) in MySQL

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sma_blink"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

Compare two table structure in MySQL

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('table1','table2')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

VALIDATE MYSQL QUERY WITHOUT EXECUTION

Prerequisite : MySQL Server 5.6+


mysql> EXPLAIN SELECT * FROM X;

Fact about hashCode() in Java

Hash code method returns integer. If range of integer finishes then also two different object will have same hashCode. So it is not necessary that two different object will have same hashCode are equal.So, If the two objects have same name, then they would be equal, but still if their string field has different length, then their hashCodes will be different.

That is why, you should always use the same fields in equals and hashCode calculation.