Useful sed / awk liners for MySQL

http://code.openark.org/blog/mysql/useful-sed-awk-liners-for-mysql

MySQL C# tutorial

http://zetcode.com/db/mysqlcsharptutorial/

InnoDB ibdata1 never shrinks after data is removed

http://bugs.mysql.com/bug.php?id=1341

Time offsets to UTC in MySQL

The offset from UTC is given in the format ±[hh]:[mm], ±[hh][mm], or ±[hh]. It is appended to the time. The offset from UTC changes with daylight saving time, e.g. a time offset in Chicago, would be "-06:00" for the winter (Central Standard Time) and "-05:00" for the summer (Central Daylight Time).

The following times all refer to the same moment: "18:30Z", "22:30+04", "1130-0700", and "15:00-03:30". Nautical time zone letters are not used with the exception of Z. To calculate UTC time one has to subtract the offset from the local time, e.g. for "15:00-03:30" do 15:00 − (−03:30) to get 18:30 UTC.

The offset can also be used in the case where the UTC time is known, but the local offset is not. In this case the offset is "-00:00", which is semantically different from "Z" or "+00:00", as these imply that UTC is the preferred reference point for those times.

SELECT CONVERT_TZ('2004-01-01 11:30:00','-07:00','+00:00');

Insert data into MySQL from a CSV file

LOAD DATA INFILE '/var/lib/mysql/btdata.csv' INTO TABLE btpl.parset FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (idsystem, name, dataurl, description);

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

mysqlimport — A Data Import Program

The mysqlimport client provides a command-line interface to the LOAD DATA INFILE SQL statement. Most options to mysqlimport correspond directly to clauses of LOAD DATA INFILE syntax.

Invoke mysqlimport like this:

shell> mysqlimport [options] db_name textfile1 [textfile2 ...]

Command invocation for a CSV file exported from Excel:

mysqlimport --fields-terminated-by=, --ignore-lines=1 -u root -p rolling mbf_symbols.csv

For each text file named on the command line, mysqlimport strips any extension from the file name and uses the result to determine the name of the table into which to import the file's contents. For example, files named patient.txt, patient.text, and patient all would be imported into a table named patient.

http://dev.mysql.com/doc/refman/5.1/en/mysqlimport.html

MySQL and REGEX

"A regular expression is a powerful way of specifying a pattern for a complex search." (Excerpt from MySQL manual: http://dev.mysql.com/doc/refman/5.1/en/regexp.html)

The financial institutions use letters to identify the months of the year in the following way:

F - January

G - February

H - March

J - April

K - May

M - June

N - July

Q - August

U - September

V - October

X - November

Z - December

So you can have a date in the form F10 which identifies January 2010 or J11 which identifies April 2011.

Let say you have a prices table in a MySQL database and you want to check that the values in the field contract in that table are correct according to the following rules:

- The first character in the field should be a letter valid for a month (F - January, G - February, etc).

- The second and third chars in the field should be a decimal digit (0..9).

You need to specify a query in SQL for MySQL that identifies all the records that have a wrong contract according to the above rules.

There are several ways of doing this in MySQL, one could be a query like the following:

SELECT * FROM prices

WHERE substr(contract, 1, 1) NOT IN ('F', 'G', 'H', 'J', 'K', 'M', 'N', 'Q', 'U', 'V', 'X', 'Z')

OR substr(contract, 2, 1) NOT IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)

OR substr(contract, 3, 1) NOT IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9);

Other query could be:

SELECT * FROM prices

WHERE substr(contract, 1, 1) NOT IN ('F', 'G', 'H', 'J', 'K', 'M', 'N', 'Q', 'U', 'V', 'X', 'Z')

OR substr(contract, 2, 1) NOT BETWEEN 0 AND 9

OR substr(contract, 3, 1) NOT BETWEEN 0 AND 9;

As you can see the queries are long so here is where you can use the power of REGEX (Regular Expressions) that MySQL implements as an extension to the SQL language.

Using REGEX the query will be:

SELECT * FROM prices

WHERE contract NOT REGEXP '^[FGHJKMNQUVXZ][0-9]{2}';

The REGEX expression is negated (NOT REGEXP) so the query is going to return values in the field contract of the prices table that don't have at the beginning (the symbol ^) a letter F or G or H, etc. (the part which is specified between square brackets, which is called a class) and 2 digits (the class [0-9] with the number of occurrences, part {2}).

As you can see REGEX provides a concise and flexible way for matching strings of text against a certain pattern.

Have a good day.

--

J. E. Aneiros

GNU/Linux User #190716 en http://counter.li.org

perl -e '$_=pack(c5,0105,0107,0123,0132,(1<<3)+2);y[A-Z][N-ZA-M];print;'

PK fingerprint: 5179 917E 5B34 F073 E11A  AFB3 4CB3 5301 4A80 F674

Using MySQL temporary table

1. Create temporary table with the settlements table structure:

CREATE TEMPORARY TABLE sett LIKE settlements;

2. Insert into the temporary table the data from the settlements for a certain date:

INSERT INTO sett (date,symbol,contract,price)
SELECT date,symbol,contract,price
FROM pltracker.settlements
WHERE date = '2010-09-03';

2a. You can do the previous two steps in only one:

CREATE TEMPORARY TABLE sett
SELECT date,symbol,contract,price
FROM settlements
WHERE date = '2010-09-03';

3. Update the date of the data at the temporary table for the specific date of the settlements:

UPDATE sett
SET date = '2010-09-06'
WHERE date = '2010-09-03';

3a. Since you are updating all the data in the table you can use this SQL statement instead:

UPDATE sett
SET date = '2010-09-06';

4. Insert data back into the settlements table:

INSERT INTO settlements (date,symbol,contract,price)
SELECT *
FROM sett;

5. Check that the new data is in place at the settlements table:

SELECT * FROM settlements
WHERE date = '2010-09-06';

6. The temporary table will be automatically drop when you close the session or you can drop it as any other table:

DROP TABLE sett;

Yesterday in MySQL query

SELECT * FROM table
WHERE date = CURDATE() - INTERVAL 1 DAY;

REPLACE vs. INSERT ... ON DUPLICATE UPDATE in MySQL

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts.

For another MySQL extension to standard SQL — that either inserts or updates — it should be use “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

REPLACE (http://dev.mysql.com/doc/refman/5.1/en/replace.html)

INSERT ... ON DUPLICATE UPDATE (http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html)

Recover MySQL Database root password

If you have set the password for root and forget or unable to recall the password, then you will need to reset the root password for MySQL.

Login as root to the Unix-like (Unix, Linux or BSD) machine with the MySQL server.

Stop the MySQL server by using either of the following command

#/etc/init.d/mysql stop

Now you need to Start MySQL server without password

# mysqld_safe --skip-grant-tables &

Connect to mysql server using mysql client with the following command

# mysql -u root

Now you should be having mysql prompt

mysql>

Now you need to Setup new MySQL root user password

mysql> use mysql;

mysql> update user set password=PASSWORD(”newrootpassword”) where user=’root’;

mysql> flush privileges;

mysql> quit

Note: Replace newrootpassword with the new root password for MySQL server. Flush Privileges is needed to making the password change effect immediately.

Now you need to Stop MySQL Server using the following command

# /etc/init.d/mysql stop

Test Your New Mysql root password

First you need to start mysql server using the following command

# /etc/init.d/mysql start

# mysql -u root -p

Now it will prompt for root password and enter your new root password.

Taken from: http://www.debianadmin.com/recover-mysql-database-root-password.html