Shantanu's Blog

You can use the "with check option" property of view to create constraint that will do the needful of checking the data.
For e.g. if I want to allow the ID 1 into the table parent, I can use "enum" datatype or create a view with appropriate where clause.

drop table child;
drop table parent;

CREATE TABLE parent(
`ID` int(11) default NULL,
`name` varchar(100) default NULL,
`city` varchar(100) default NULL,
primary key (name)
) ENGINE=InnoDB;

create table child(
username varchar(100) not null,
FOREIGN KEY (username) REFERENCES parent(name) on update cascade,
primary key (username)
) ENGINE=InnoDB;

insert into parent values (1, 'abc', 'delhi');
insert into child values ('abc');

create or replace view myview as select * from parent where ID > 0 and ID < 2 with check option;

mysql>insert into myview values (3, 'qqabc', 'wwdelhi');
ERROR 1369 (HY000): CHECK OPTION failed 'test.myview'

mysql>insert into myview values (1, 'qqabc', 'wwdelhi');
Query OK, 1 row affected (0.02 sec)

Labels: mysql tips

Finding rows those are NOT 10 digits long

I need to find the rows those are not 10 digits in length and must include a digit and not a alphabet.

CREATE TABLE mobile (mno varchar(100) default NULL);
INSERT INTO mobile VALUES ('223456780'),('2234567890'),('4234567890'),('6234567890'),('22345678aaaa0'),('123456789a');

mysql> select * from mobile;
+---------------+
| mno |
+---------------+
| 223456780 |
| 2234567890 |
| 4234567890 |
| 6234567890 |
| 22345678aaaa0 |
| 123456789a |
+---------------+
6 rows in set (0.00 sec)

mysql> select * from mobile WHERE length(mno) != 10;
+---------------+
| mno |
+---------------+
| 223456780 |
| 22345678aaaa0 |
+---------------+
2 rows in set (0.00 sec)

mysql> select * from mobile WHERE mno NOT RLIKE '[0-9]{10}';
+---------------+
| mno |
+---------------+
| 223456780 |
| 22345678aaaa0 |
| 123456789a |
+---------------+
3 rows in set (0.00 sec)

Labels: mysql case study

I can write the following query if I know the exact column name where I expect to find the matching value.
select * from parent where city = 'Delhi'
But it is interesting to know that grep can be used at the command prompt that will return only the records where that appear.

mysql>pager grep Delhi
PAGER set to 'grep Delhi'

mysql>select * from parent;
| 2 | Amar | Delhi |
| 5 | Akbar | Delhi |
| 8 | Anthony | Delhi |
10 rows in set (0.00 sec)

mysql>pager
Default pager wasn't set, using stdout.

Labels: mysql tips

Selecting top N records per group is not as easy as it seems. The query may get as complex as you want.

http://code.openark.org/blog/mysql/sql-selecting-top-n-records-per-group

Here is how I would solve it by taking help of composite index with one of the column being auto_id.
InnoDB table types do not support such indexes though.

drop table if exists Country_sort;

CREATE TABLE `Country_sort` (
id int not null auto_increment,
`Code` char(3) NOT NULL default '',
`Name` char(52) NOT NULL default '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
`Region` char(26) NOT NULL default '',
`SurfaceArea` float(10,2) NOT NULL default '0.00',
`IndepYear` smallint(6) default NULL,
`Population` int(11) NOT NULL default '0',
`LifeExpectancy` float(3,1) default NULL,
`GNP` float(10,2) default NULL,
`GNPOld` float(10,2) default NULL,
`LocalName` char(45) NOT NULL default '',
`GovernmentForm` char(45) NOT NULL default '',
`HeadOfState` char(60) default NULL,
`Capital` int(11) default NULL,
`Code2` char(2) NOT NULL default '',
PRIMARY KEY (Continent, id)
) ENGINE=MyISAM;

insert into Country_sort select NULL, Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LifeExpectancy, GNP, GNPOld, LocalName, GovernmentForm, HeadOfState, Capital, Code2 from Country order by Continent, SurfaceArea desc;

select Continent, name from Country_sort where ID = 1 order by Continent;

select Continent, name, SurfaceArea, Population from Country_sort where ID <= 5 order by Continent, SurfaceArea desc;

Labels: mysql tips

MySQL by default, ignores capital and small letters in the data:

mysql> create table todel (description varchar(100) COLLATE utf8_bin);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into todel (description) values ('This is MIXED case');
Query OK, 1 row affected (0.00 sec)

mysql> select * from todel where description like '%MIXED%';
+--------------------+
| description |
+--------------------+
| This is MIXED case |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from todel where description like '%mixed%';
+--------------------+
| description |
+--------------------+
| This is MIXED case |
+--------------------+
1 row in set (0.00 sec)

_____

If you want mysql to consider the case, you have to use the "binary" keyword as shown below. The comparison is now case sensitive.

mysql> select * from todel where binary description like '%mixed%';
Empty set (0.00 sec)

mysql> select * from todel where binary description like '%MIXED%';
+--------------------+
| description |
+--------------------+
| This is MIXED case |
+--------------------+
1 row in set (0.00 sec)

_____

If you want all the queries on that column (for e.g. description) to be case sensitive, change the collation to binary in the create table statement.

mysql> create table todel (description varchar(100) COLLATE utf8_bin);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into todel (description) values ('This is MIXED case');
Query OK, 1 row affected (0.00 sec)

mysql> select * from todel where description like '%mixed%';
Empty set (0.00 sec)

mysql> select * from todel where description like '%MIXED%';
+--------------------+
| description |
+--------------------+
| This is MIXED case |
+--------------------+
1 row in set (0.00 sec)

_____

It is recommended NOT to use the utf8_bin collation type for any column. If your table already has one, get rid of it using "alter table modify" statement.

mysql> show create table todel\G
*************************** 1. row ***************************
Table: todel
Create Table: CREATE TABLE `todel` (
`description` varchar(100) character set utf8 collate utf8_bin default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> alter table todel modify description varchar(100);

mysql> show create table todel\G
*************************** 1. row ***************************
Table: todel
Create Table: CREATE TABLE `todel` (
`description` varchar(100) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> alter table todel modify description varchar(100) collate utf8_bin;

mysql> show create table todel\G
*************************** 1. row ***************************
Table: todel
Create Table: CREATE TABLE `todel` (
`description` varchar(100) character set utf8 collate utf8_bin default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Labels: mysql tips

rollbacks usually let you go back to the start of the transaction, but not necessarily if you use savepoints.
mysql> rollback;

will undo all the statements till the beginning of the transaction, whereas
mysql> rollback to savepoint savepoint_one;

Will revert back to the saved position in the transaction.
You can run the following script to see how savepoints work in transactions/ Stored procedures.


drop table if exists test.savepoint_test;
drop procedure if exists second_fail;
drop procedure if exists prc_work;
CREATE TABLE test.savepoint_test (
id int not null default '0',
name varchar(100),
primary key (id)
)engine=InnoDB;
insert into test.savepoint_test values ('1', 'jack');
insert into test.savepoint_test values ('2', 'akbar');
select * from test.savepoint_test;

start transaction;
update test.savepoint_test set name = 'amar' where id = 1;
select * from savepoint_test;

savepoint savepoint_one;
update savepoint_test set name = 'jill' where id = 2;

select * from savepoint_test;
rollback to savepoint_one;

select * from savepoint_test;

commit;

select 'using savepoints in stored procedures' as message;

delimiter $$
drop procedure if exists second_fail$$
CREATE PROCEDURE second_fail()
BEGIN
INSERT into test.savepoint_test values ('3', 'anthony');
INSERT into test.savepoint_test values ('2', 'xyz');
END;
$$

select 'ID 3 manages to get into the table but ID 2 fails due to duplicate primary key error' as message $$
drop procedure if exists prc_work$$
CREATE PROCEDURE prc_work()
BEGIN
SAVEPOINT sp_prc_work;
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_prc_work;
INSERT into test.savepoint_test values ('4', 'krishna');
INSERT into test.savepoint_test values ('2', 'abc');
END;
END;
$$

select 'ID 4 qualifies to be inserted but fails because ID 2 fails and the entire transaction is reverted to the savepoint' as message $$

delimiter ;

start transaction;
call second_fail();
select * from test.savepoint_test;

call prc_work();
select * from test.savepoint_test;
commit;

Labels: InnoDB, mysql, mysql tips