MYSQL LIKE match whole word only

Mysql LIKE clause match exact whole word only while searching. Search the words with space in mysql database.

If you want to search for exact word using LIKE clause, you can’t do it just using percentage (%) sign around the keyword as it will search for partial words too. For example, the below query will match all the words which contain “some”:

SELECT * FROM tablename WHERE columnname LIKE '%some%';

It will find all the instances where it finds some, as a whole or a part. some, someone, something, handsome, isometric are all MATCHED using above query. Ofcourse there will be many results which are UNRELATED.

But what if you only want some, as an exact word WITH space and WITHOUT any extra heading and trailing characters?

This query will do exactly that:

SELECT * FROM tablename WHERE columnname = 'some' OR columnname LIKE '% some' OR columnname LIKE 'some %' OR columnname LIKE '% some %';

In above query we are making sure that all the some instances are retrieved, whether they are present in the START or in the END or in the MIDDLE of the columnname field.

Mysql root password reset or create

Mysql root password reset easily by following below instructions. For those who want to create (if no root user in mysql) or reset the password for the Root user in Mysql (in Linux), this post is definately going to help you. I had a tough time in getting this to work, so thought to share it with anyone facing the same problem.

You need to use –skip-grant-tables if you don’t know mysql password to login. You can use it like this:

/usr/bin/mysqld_safe –skip-grant-tables &

Once you run the above command on terminal, write mysql and hit enter. That’s because you already said Mysql that you want to skip all the privileges checking and allowing you with all the database access without password.

You should be inside mysql now. Now execute the command use mysql; because we are going to create/change the user in “mysql” database.
Continue reading »

Mysql: Find column name in any table(s) having it in whole database

There may be instances where we want to find in how many tables of some particular database some column (field) occurs. Or you know that some column field existed but don’t remember in which table of a database, what will you do? And if there are hundreds of tables, it would be nightmare to go through each table schema and searching.

Below Mysql query will get you all the tables where the specified column occurs in some database.

SELECT table_name, column_name from information_schema.columns WHERE column_name LIKE '%column_name_to_search%';

Remember, don’t use % before column_name_to_search if you know the starting characters of that column. It slows down, as Mysql will not use any indexes for fields searched with leading wildcard (%).

Mysql delete duplicate records leaving one

Mysql delete duplicate records leaving one row. Consider that there are many duplicate records in the table and you want to remove it. Now, we can select it by applying GROUP BY but the question is how to delete all the duplicate records (of field some_id below) EXCEPT one that should be there. So, lets say I have following table with duplicate data as shown:
Tablename: test

unique_id | some_id

--------------------

1         | 1

2         | 1

3         | 2

4         | 2

5         | 1

Continue reading »

Magento add admin user in MySQL

In Magento, if you want to create a new user directly in Mysql, it’s not that easy to insert one record in admin_user table.
You need to also update the privileges and inserting new admin’s roles.
So here is a Mysql script which will create a new admin user with all privileges.

Replace FIRSTNAME, LASTNAME, EMAIL, USERNAME, PASSWORD with your desired values.

insert into admin_user

select

(select max(user_id) + 1 from admin_user) user_id,

'FIRSTNAME' first_name,

'LASTNAME' last_name,

'TEST@EMAIL.COM' email,

'USERNAME' username,

MD5('PASSWORD') password,

now() created,

NULL modified,

NULL logdate,

0 lognum,

0 reload_acl_flag,

1 is_active,

(select max(extra) from admin_user where extra is not null) extra,

NULL,

NULL;

insert into admin_role

select

(select max(role_id) + 1 from admin_role) role_id,

(select role_id from admin_role where role_name = 'Administrators') parent_id,

2 tree_level,

0 sort_order,

'U' role_type,

(select user_id from admin_user where username = 'USERNAME') user_id,

'USERNAME' role_name