06.03 2007
17:32

MySQL - Transactions

Effects of transaction we can watching all the time by SELECT instructions. I case when we see some anomaly, we can go back to place where transaction was saved or totally rollback this transaction. MySQL database supports transactions by short time. Used by defaults MyISAM as table type, haven't any possibilities to run transactions. This functionality was appear after launch InnoDB tables type.

Preparing tables

Before starts transaction, we need to prepare table in database in adequate way. Table which we will be use to make transactions should has InnoDB type. Actually this is only one (against not fully integrated BerkleyDB) type of table in MySQL database, which has support for transactions. Here is sample structure of table, which we will be using in other samples.

CREATE TABLE `trans` (
`id` INT NOT NULL AUTO_INCREMENT ,
`re` INT NOT NULL ,
`txt` VARCHAR( 100 ) NOT NULL ,
PRIMARY KEY ( `id` ) 
) TYPE = innodb;

Starting transactions

To start transaction in MySQL database we should use command START TRANSACTION; Before begin of transaction we can also state level of isolation by command SET TRANSACTION ISOLATION LEVEL. For this command are accessible four parameters: { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }. If we didn't choose any isolation level, by default will be REPEATABLE READ option. Here some sample for beginning of transaction:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

Saving state of transaction

Saving state of transaction isn't a necessary step. It is used in transactions when can appear some mistakes or problems. Then is a possibility to go back to earlier saved state. To save state of transaction we use command SAVEPOINT point; where point is a name of position to save.

Finishing transactions

To finish transaction we use command COMMIT; After finish of transaction all executed operations will be saved in database, without any possibility to call them off.

Breaking transaction

Sometimes happens situations when we must with some reason break transaction. To made this exists command ROLLBACK; When it will be used all realized commands by transaction time will be call off and contents of database will be not change. This command makes possibilities to go back to earlier saved state of transaction. In this case command will be looks like this: ROLLBACK TO point; where point is name of last saved position.

{newpage}

Samples of some transactions

Transaction 1:

START TRANSACTION;
INSERT INTO trans (re,txt) VALUES (0,'rek 1');
INSERT INTO trans (re,txt) VALUES (0,'rek 2');

Preview state of table in case of successful finish of transaction:

SELECT * FROM trans;
+----+----+-------+
| id | re | txt   |
+----+----+-------+
|  1 |  0 | rek 1 |
|  2 |  0 | rek 2 |
+----+----+-------+

COMMIT;

Here is end of transaction – added records now are in database.

Transaction 2:

START TRANSACTION;
INSERT INTO trans (re,txt) VALUES (5,'trans 2');

Preview of transaction state:

SELECT * FROM trans;
+----+----+---------+
| id | re | txt     |
+----+----+---------+
|  1 |  0 | rek 1   |
|  2 |  0 | rek 2   |
|  3 |  5 | trans 2 |
+----+----+---------+

ROLLBACK;

Here transaction was break. To show that the entry wasn't added to database we execute again command:

SELECT * FROM trans;
+----+----+-------+
| id | re | txt   |
+----+----+-------+
|  1 |  0 | rek 1 |
|  2 |  0 | rek 2 |
+----+----+-------+

Transaction 3:

START TRANSACTION;
UPDATE trans SET re=5 WHERE id=2;
SAVEPOINT krok1;
INSERT INTO trans (re,txt) VALUES (5,'trans 3');

Check state of transaction:

SELECT * FROM trans;
+----+----+---------+
| id | re | txt     |
+----+----+---------+
|  1 |  0 | rek 1   |
|  2 |  5 | rek 2   |
|  4 |  5 | trans 3 |
+----+----+---------+

ROLLBACK TO krok1;
COMMIT;

Transaction was finish successful, but before finish of it we go back to earlier saved point transaction state. In result of this command add new record was called off:

SELECT * FROM trans;
+----+----+-------+
| id | re | txt   |
+----+----+-------+
|  1 |  0 | rek 1 |
|  2 |  5 | rek 2 |
+----+----+-------+

06.03 2007
17:19

MySQL - Regular expressions

Meta-chars of regular expressions for MySQL

Although many languages lets to use regular extensions that, this chars can be used for specified programing languages. Here is list of chars for MySQL.

. - any char
^ - begin of sequence
$ - end of sequence
[...] - any char form collection (...)
[^...] - any char from out of collection (...)
[:k:] - any char from class k
a* - number of appears char a is 0 or more
a+ - number of appears char a is 1 or more
a? - number of appears char a is 0 or 1
a{5} – number of appears char a is 5
a{1,5} – number of appears char a is between 1 and 5
a{5,} - number of appears char a is 5 or more
a|b – appear char a or b
(...) - define of store extension

To inform MySQL database about use in query regular extensions should be used command REGEXP. MySQL database doesn't distinguish chars size inside query, so decision about using small caps or all caps while creating query depends on user. There are some cases when char to find is a part of regular extension (ex. Searching char [). In this case before use of char should be added symbol \ (ex. \[).

{newpage}

Samples of use regular extensions

Using regular extensions isn't difficult, how this can looks like for beginner users. Here i presents a few issues connected with MySQL queries with regular extensions.

Searching records which end on 'a':

SELECT id,txt1 FROM test.test 
 WHERE txt1 REGEXP 'a$';

Searching records which begin on a digit:

SELECT id,txt1 FROM test.test 
 WHERE txt1 REGEXP '^[:0-9:]';

Searching records with sequence "jak"

SELECT id,txt1 FROM test.test 
 WHERE txt1 REGEXP 'jak';

Searching records which first word begins on 's':

SELECT id,txt2 FROM test.test 
 WHERE txt2 REGEXP '^([[:<:]]s)';

Searching records longer than one word:

SELECT id,txt2 FROM test.test 
 WHERE txt2 REGEXP '^.+ ';

Searching records with website addresses:

SELECT id,txt1 FROM test.test 
 WHERE txt1 REGEXP '(http://)+[a-z0-9]+(\.[a-z0-9])*(\.[a-z])';

Searching records with email addresses:

SELECT id,txt1 FROM test.test 
 WHERE txt1 REGEXP '([a-z0-9]+@{1}[a-z0-9]+(\.[a-z][0-9])*(\.[a-z]))';

06.03 2007
17:11

MySQL - Date processing functions

Basic functions

Getting current date: CURDATE(), or CURRENT_DATE. Functions returns actually date on MySQL database server in RRRR-MM-DD format. In case of use command SELECT CURDATE(); we receive 2006-12-13.

Getting current time: CURTIME(), or CURRENT_TIME. Functions returns actually time on MySQL database server in HH:MI:SS format. In case of use command SELECT CURTIME(); we receive 00:16:24.

Getting current date and time NOW(), SYSDATE(), or CURRENT_TIMESTAMP. Functions returns actually date and time in RRRR-MM-DD HH:MI:SS format. In case of use command SELECT NOW(); we receive 2006-12-13 00:21:49.

Adding intervals to date: DATE_ADD(date, INTERVAL unit). This function is adding to entered date specific number of units. In case of use command SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); we receive current date with one day older.

Subtraction intervals of date: DATE_SUB(date, INTERVAL unit). This function is subtracting from entered date specific number of units. In case of use command SELECT DATE_SUB(NOW(), INTERVAL 1 DAY); we receive current date with one day younger.

Date elements

DAYOFWEEK(date) - returns day of week as number
WEEKDAY(date) - returns day of week as number (0 – Monday)
DAYOFMONTH(date) - returns day of month as number
DAYOFYEAR(date) - returns day of year as number
MONTH(date) – returns number of month in year
DAYNAME(date) – returns full name of day of week
MONTHNAME(date) – returns full name of month
QUARTER(date) – returns number of quarter
WEEK(date) – returns number of week in year
YEAR(date) - returns year
HOUR(date) – returns hour
MINUTE(date) – returns number of minutes
SECOND(date) - returns number of seconds
TO_DAYS(date) – returns number of days which left from beginning of our era.
FROM_DAYS(days) – returns date, which show number of days from beginning of our era.

{newpage}

Changing date format

To changing format of returned by MySQL database date is function DATE_FORMAT(date,format). Sample of use: SELECT DATE_FORMAT(NOW(),'%d %M %Y'); returns date in 13 December 2006 format. Here is full list of MySQL date marks:

%a – returns short name of week
%b – returns short name of year
%c – returns number of month
%D – returns day of month
%d – returns day of month in two digits format
%e – returns day of month
%f - returns milliseconds number
%H – returns hour in 24 hours format, two digits
%h – returns hour in12 hours format, two digits
%i – returns number of minutes
%j - returns number of day in year, three digits
%k – returns hour in 24 hours format
%l – returns hour in 12 hours format
%M – returns full name of month
%m – returns number of month
%p – returns AM or PM
%r - returns time in 12 hours format
%S – returns number of seconds
%T – returns time in 24 hours format
%U – returns number of week (Sunday is a first day, counting from 00)
%u – returns number of week (Monday is a first day, counting from 00)
%V – returns number of week (Sunday is a first day, counting from 01)
%v – returns number of week (Monday is a first day, counting from 01)
%W – returns full name day of week
%X – returns year of selected week (Sunday is a first day)
%x – returns year of selected week (Monday is a first day)
%Y – returns year in four digits format
%y – returns year in two digits format

{newpage}

Few samples of use

The above operations haven't any bigger usage in operating on database. Usually they are used with condition WHERE. It's let accurately state which data will be downloaded, updated or deleted from database. It's often use to add new records to database too.

Adding new data with current date:

INSERT INTO test.test (vol,date) VALUES('aaa',NOW());

Adding new data with tomorrow date:

INSERT INTO test.test (vol,date) 
 VALUES('aaa',DATE_ADD(NOW(),INTERVAL 1 DAY));

Adding new data with date from last year:

INSERT INTO test.test (vol,date)
 VALUES('aaa',DATE_SUB(NOW(),INTERVAL 1 YEAR));

Getting data older than one year:

SELECT * FROM test.test 
 WHERE test.date < DATE_SUB(NOW(),INTERVAL 1 YEAR);

Getting data between tomorrow - yesterday:

SELECT * FROM test.test 
 WHERE test.date > DATE_SUB(NOW(),INTERVAL 1 DAY) 
 AND test.date < DATE_ADD(NOW(),INTERVAL 1 DAY);

Getting data with yesterday date:

SELECT * FROM test.test WHERE test.date LIKE 
 CONCAT(DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y-%m-%d'),'%');

Showing date without time:

SELECT id, DATE_FORMAT(date,'%Y-%m-%d') as date FROM test.test;

Adding to date one year:

UPDATE test.test SET date = 
 DATE_ADD(date, INTERVAL 1 YEAR) WHERE id='1';

Getting difference between current date and chosen:

SELECT id,TO_DAYS(NOW()) - TO_DAYS(date)
 as difference FROM test.test;

Getting day of week:

SELECT id,DAYNAME(date) as day_of_week FROM test.test;

MySQL - Transactions

Preparing tables

Starting transactions

Saving state of transaction

Finishing transactions

Breaking transaction

Samples of some transactions

MySQL - Regular expressions

Meta-chars of regular expressions for MySQL

Samples of use regular extensions

MySQL - Date processing functions

Basic functions

Date elements

Changing date format

Few samples of use

About me

Calumma Player

GoRapidDownloader

jMPD