Mysql: Count Occurrence of a particular character in a given String

Count Occurrence of Character in a String Using MySQL « In Piscean’s Heart

Since there is no inbuilt MySQL function to count the occurrence of a character in string, we can do it by using these steps:

1. Counting the number of characters in the original string

2. Temporarily ‘deleting’ the character you want to count and count the string again

3. Subtract the first count from the second to get the number of occurrences

Example:

SELECT LENGTH('foobarfoobarfoobar') - LENGTH(REPLACE('foobarfoobarfoobar', 'b', '')) AS `occurrences`
--> Result: 3

In this example ‘b’ is the string you want to count the number of occurrences.

Mysql: Client Tips 2: Running Shell Commands from the Commandline

Ok, As you know I am trying to explore more of the mysql command line and exploring its features. I have already written about how to change default mysql prompt using the command "prompt" in one of my previous articles.

Continuing this series. I am focusing today on the "system" command or "/!".

Well, I always have some of the sql source files and I execute it from the mysql command line. But I just keep forgetting their names. And most of the times I either close the mysql session and do 'ls' at the command line or move the mysql session in background and do a 'ls'.

Mysql cli has this very useful command called "system" using which you can execute shell commands from mysql client itself.

just type

  system ls -l

or

  \! ls -l

Some of us, who are familiar with Vim, will like this "\!" version a lot, because we are already using ! to run system commands there.

You can run almost all system commands using either of system or '\!'

Some examples:

mysql:root@test [14:26:19 pm]$ \! pwd
/home/owner

mysql:root@test [14:29:33 pm]$ \! date
Thu Mar 26 14:29:46 SGT 2009

mysql:root@test [14:29:46 pm]$ \! bash
owner@rocky-desktop:~$ exit
mysql:root@test [14:30:12 pm]$

You see the last one is quiet interesting. You can actually open a shell using this command perform some tasks. Just exit from the shell and you are back in the mysql world.

Liked it ! Start playing with mysql client system command.

Mysql: Comments Trick

Nice article by Eli White. Now you can understand easily all those comments that you see in the mysqldump output.


MySQL Trick with Comments

MySQL has a custom extension (if you want to call it that) to it’s commenting system, which actually allows you to make conditional commands. The mysqldump program that many people use to back up their databases uses this concept heavily, which is where I first ran into it.

In it’s simplest form, this allows you to place commands in a SQL file that only MySQL will run:

/*! MySQL specific code */

This allows you to make a single SQL file, that not only will execute correctly (and in detail) on MySQL, but it will also work on any ANSI compatible database, since you’ve hidden any of the non-ANSI parts in MySQL conditional comments. So for example, MySQL allows you to set a variable declaring that all names will be a certain character set, such as UTF-8, and therefore you can do:

/*! SET NAMES utf8 */;

MySQL: Query to List Down All Schema Objects

Below is a simple query to ease your day to day work. It simply lists down all the objects of a MySQL Schema. The things that you are mostly interested in e.g. Tables, Views, Routines, Indexes, Triggers and so on.

Just replace "[your-schema-name-here]" in the following query with your schema name. Hope it comes handy to some of you out there.

Update:05/27/2015 - Adding Github Gist for the script

SELECT OBJECT_TYPE
,OBJECT_SCHEMA
,OBJECT_NAME
FROM (
SELECT 'TABLE' AS OBJECT_TYPE
,TABLE_NAME AS OBJECT_NAME
,TABLE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.TABLES

UNION

SELECT 'VIEW' AS OBJECT_TYPE
,TABLE_NAME AS OBJECT_NAME
,TABLE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.VIEWS

UNION

SELECT 'INDEX[Type:Name:Table]' AS OBJECT_TYPE
,CONCAT (
CONSTRAINT_TYPE
,' : '
,CONSTRAINT_NAME
,' : '
,TABLE_NAME
) AS OBJECT_NAME
,TABLE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.TABLE_CONSTRAINTS

UNION

SELECT ROUTINE_TYPE AS OBJECT_TYPE
,ROUTINE_NAME AS OBJECT_NAME
,ROUTINE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.ROUTINES

UNION

SELECT 'TRIGGER[Schema:Object]' AS OBJECT_TYPE
,CONCAT (
TRIGGER_NAME
,' : '
,EVENT_OBJECT_SCHEMA
,' : '
,EVENT_OBJECT_TABLE
) AS OBJECT_NAME
,TRIGGER_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.triggers
) R
WHERE R.OBJECT_SCHEMA = [your-schema-name-here];

Mysql: InnoDB: Transaction Models and Isolation Levels.

Inconsistency can play havoc while executing concurrent connection.
To make our life easy MySQL's InnoDB supports the following isolation levels.

• Isolation level 1 (read uncommitted):- Can read the *dirty* update done by client 1. It can cause Inconsistent behaviour in case client 1 roll-backs. This isolation level also allow non-repeatable, phantom reads.
• Isolation level 2 (read committed):- Can't read the *dirty* update done by client 1. Hence at this isolation level changes propagate only after the transaction is committed. However this isolation level allows non-repeatable and phantom reads.
• Isolation level 3 (repeatable read):- Can't read the *dirty* update done by client 1. Also at this isolation level read locks are acquired on data. Thus reads are repeatable at this isolation level. Phantom reads however cause problem because range locks are not acquired at that level
• Isolation level 4 (serializable):- Transaction is serialized, all three cases are handled, however slow in performance as at every level locks are acquired.

Default Isolation level for Mysql is Repeatable Read

How to check Isolation level for Mysql:

To know isolation level for current session :-

mysql> select @@tx_isolation;

To check isolation level globally:-

     mysql> select @@global.tx_isolation;

How to change Isolation level for Mysql:

Here is the command to do so:-

mysql> set [session|global] transaction isolation level [read uncommitted| read committed | read repeatable | serializable] ;

Note:
1. Use global if you want to set isolation level for all the new connections made from that point onwards. However it won't change isolation level of existing connections.

How to change Isolation level at service startup:

    Use --transaction-isolation=level, example

$ mysqld --transaction-isolation={ READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE } start

Some examples to clear the air:-

Basic Setup

    * Client one reads device model information and can update device model information.
    * Client two can also read and update device model information.

Test Cases:-

Example 1:- ( Dirty Read ) Client One:

> start transactions;

> select dev_model from client;

| dev_model|
|   1      |
|   2      |
|   3      |

> update dev_model set dev_model=4 where dev_model=1;

Client Two:-

> start transactions;

> select dev_model from client;  
  ( At Isolation level 1 (read uncommitted) )
    |dev_model|
    |   4     |
    |   2     |
    |   3     |

   ( At Isolation level 2 (read committed) )
    |dev_model|
    |   1     |
    |   2     |
    |   3     |
  ( Same as above for level 3, 4)
 

Example 2 (Non-Repeatable Read):-

client 1:-

> start transaction;

> select dev_model from client

|dev_model|
|   1     |
|   2     |

client 2:

> start transaction
> update client set dev_model=4 where dev_model=1
> commit||

client 1:

> select dev_model from client

 
At isolation 1,2:-
|dev_model|
|  1      |
|  4      |
          
As we can see for client 1, reads are non-repeatable.

At isolation 3,4:-
|dev_model|
|  1      |
|  2      |

Thus at isolation level 3 and above reads are repeatable.


As read locks are acquired by client 1, the data does not change even when client 2 commits. Client 2 transaction needs to wait for client one to commit first thus making transaction 1, 2 as serialized. Thus at isolation level 3, the reads are repeatable.

* Isolation level 4 (serializable): At isolation level 3, . for example:-

Client 1:-

> start transaction;

> select dev_model from client where client_id > 5 and client_id < 10
| dev_model |
|   1       |
|   2       |

client 2:-

> start transaction;

> insert into client (client_id, dev_model) values (7, 10);

> commit

client 1:-

>  select dev_model from client where client_id > 5 and client_id < 10 ;

(Isolation level 1,2,3)
|dev_model|
|  1      |
|  2      |
|  10     |

( Isolation level 4)
|dev_model|
|   1     |
|   2     |

Thus transaction 1 take place as if transaction 2 hasn't occurred YET


Thus a phantom read can occur at isolation level 3, however at isolation level 4 i.e serializable the transactions are serialized and all the three cases, i.e. phantom read, non-repeatable reads and dirty read are prevented by acquiring locks for range, data and transaction.

Mysql: Cross tabulation: Very useful article from mysql tech-resources

MySQL :: MySQL Wizardry

Cross tabulations are statistical reports where you de-normalize your data and show results grouped by one field, having one column for each distinct value of a second field.



Basic problem definition. Starting from a list of values, we want to group them by field A and create a column for each distinct value of field B.



The desired result is a table with one column for field A, several columns for each value of field B, and a total column.

Mysql: Oracle users looking for Rownum in mysql !!

Sadly, MySQL doesn't have (yet) the ROWNUM function. But a simple playing around with variables will give you the desired result.

Here is an example: (Src: dzone)
mysql code

SELECT @rownum := @rownum + 1 as rownum, t.* FROM some_table t, (SELECT @rownum := 0) r

Mysql: Change Default Prompt

Not so long ago, I used to use mysql command line client in a very traditional way.
you know, like a simple login using

mysql -u[username] -p[password] -h [hostname] -D [database]

Recently just out of curiousity  I typed "help" at the  mysql prompt. It gave me a whole list of commands. Then I realized that I can do a lot more with the mysql command line.

mysql> help


List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?           (\?) Synonym for `help'.
clear      (\c) Clear command.
connect  (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
edit        (\e) Edit command with $EDITOR.
ego        (\G) Send command to mysql server, display result vertically.
exit        (\q) Exit mysql. Same as quit.
go         (\g) Send command to mysql server.
help       (\h) Display this help.
nopager  (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print       (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit         (\q) Quit mysql.
rehash     (\#) Rebuild completion hash.
source     (\.) Execute an SQL script file. Takes a file name as an argument.
status      (\s) Get status information from the server.
system     (\!) Execute a system shell command.
tee          (\T) Set outfile [to_outfile]. Append everything into given outfile.
use          (\u) Use another database. Takes database name as argument.
charset     (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings   (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

Looking at the list I realize I was using "\q", "\c" and "\G" for quiet sometime now. But wasnt aware of other commands. While I am investigating other commands. Let me tell you the most interesting command, which caught my attention immediately.

It is  PROMPT. It helps you in customizing the default mysql prompt i.e. "mysql >"

To change the prompt you just need to enter prompt command with what value  you want

e.g. prompt "hello world"

and your prompt will be set to "hello world". But I guess you would like to change it to something meaningful. Here comes the special sequences, to help you with this.

mysql> prompt [mysql:\u@\d][\R:\m:\s \P] $
PROMPT set to '[mysql:\u@\d][\R:\m:\s \P] $'

and now your prompt will look like:

[mysql:root@test][10:55:58 am] $

You see I have used "\u" "\d" "\R" \m \s, \P .. They mean, in that order, 'current user name', 'current database', 'hrs of current time', 'minutes for current time', 'seconds for current time', 'AM/PM indicator'. These are the special sequences I was talking about.

There is a whole list of special sequences, you can find it at mysql documentation page.  So what are you waiting now. Go try out and have your very own customized mysql prompt :D

Update: To make sure that you get the same prompt everytime, you can modify mysql config file (typically /etc/my.cnf) or create an file name .my.cnf in your home directory with a content similar to this:

[mysql]
prompt=mysql:\\u@\\d [\\R:\\m:\\s \\P]$\\_

Mysql: Find max and minimum date in a month

Recently I was looking for a quick way to find the first and last day of a  month, given any date from that month.
Michael Williamson seem to have a quick and fugly solution, which works quiet well.

Thanks Miachael.


Michael’s Blog » Blog Archive » MySQL number of days in a month

select date_add(concat(year(curdate()),'-',month(curdate()),'-','1'), interval 0 day) as mo_start,
date_sub(date_add(concat(year(curdate()),'-',month(curdate()),'-','1'), interval 1 month), interval 1 day) as mo_end;

Update: The same thing can be achieved by replacing curdate() with "now()". Here is the modified query.

select date_add(concat(year(now()),'-',month(now()),'-','1'), interval 0 day) as mo_start,
date_sub(date_add(concat(year(now()),'-',month(now()),'-','1'), interval 1 month), interval 1 day) as mo_end;

How to Number Rows in MySQL

I was looking for a way to number the row in a sql select result. A search lead me to the following.

How to number rows in MySQL at Xaprb

set @type = ''; set @num = 1; select type, variety, @num := if(@type = type, @num + 1, 1) as row_number, @type := type as dummy from fruits;

Mysql: Bulk Inserts involving updates

Lets think of a situation: where you have to bulk insert,
but there might be some data which is already there in the db, and you just want to modify it.
In such cases, something like the following query will help you.

Insert
 Into t1 (id,val1,va2)
 Values (1,1,1),(2,2,2),(3,3,3)
On Duplicate Key Update
 val1= val1+ values(val1),
 val2= val2 + values(val2)
The key is to use the clause "On Duplicate Key Update"

Mysql: Show user defined functions for some schema

If you have to look for the user defined functions in a mysql schema, here is the sample syntax to do so:

select
*
from
information_schema.ROUTINES
where
ROUTINE_SCHEMA=[schemaname] \G

PS: I use "\G" for better formatting, its not something which is necessary.

Mysql: Find Indexes on a Table

Select
COLUMN_NAME,
INDEX_NAME,
INDEX_TYPE
From
information_schema.STATISTICS
where
TABLE_NAME=[table name]
and TABLE_SCHEMA=[schema name]