Browsing articles tagged with "mysql Archives - Kalpesh Mehta"
Jun 27, 2013
kalpesh

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”:

1
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:

1
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.

Jan 8, 2013
kalpesh

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 »

Aug 3, 2012
kalpesh

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.

1
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 (%).

Jul 24, 2012
kalpesh

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

1
2
3
4
5
6
7
unique_id | some_id
--------------------
1         | 1
2         | 1
3         | 2
4         | 2
5         | 1

Continue reading »

Feb 12, 2012
kalpesh

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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

Welcome to my Blog

Kalpesh MehtaHelping Magento developers in their day-to-day development problems since 2011. Most of the problems and solutions here are my own experiences while working on different projects. Enjoy the blog and don't forget to throw comments and likes/+1's/tweets on posts you like. Thanks for visiting!

Certifications