William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

Tag Archives: mysql

php MySQLi: MySQL Improved Extension

There are three main API options when considering connecting to a MySQL database server:

  1. PHP’s MySQL Extension
  2. PHP’s mysqli Extension
  3. PHP Data Objects (PDO)

Each has its own advantages and disadvantages. By default we use ‘PHP MySQL extension’. Here focus on the second – MySQLi extension:
The mysqli extension is built using the PHP extension framework, its source code is located in the directory ext/mysqli.

According to wiki, MySQLi is an improved version of the older PHP MySQL driver, offering various benefits.
The MySQLi extension provides various benefits with respect to its predecessor, the most prominent of which are:

  • An object-oriented interface
  • Support for prepared statements
  • Support for multiple statements
  • Support for transactions
  • Enhanced debugging support
  • Embedded server support

The following is a quick example from php.net/mysqli‘s document page:

<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") "
      . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!($stmt = $mysqli->prepare("SELECT id, label FROM test ORDER BY id ASC"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$stmt->execute()) {
     echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!($res = $stmt->get_result())) {
    echo "Getting result set failed: (" . $stmt->errno . ") " . $stmt->error;
}

var_dump($res->fetch_all());
?>
Advertisements

MySQL: update column by using regexp

1. MySQL, update column by using regexp

I want to update the values of a MySQL column, e.g. from:
http://www.example.com/news/ent//news/2002/09/02/ent-12.html?page=1223
to:
http://www.example.com/news/2002/09/02/ent-12.html?page=1223
For a huge table, if using:

  1. $ mysqldump -u user -p database table > dump.sql
  2. $ find /path/to/dump.sql -type f -exec sed -i ‘s/old_string/new_string/g’ {} \;
  3. $ mysqlimport -u user -p database table < dump.sql

It would make processing complex and easy to occur errors. My solution is very simple: just using MySQL’s substring(), concat(), they works very well: it takes around 10 seconds to process tens of thousands data.

update contents 
 set url=concat('http://www.example.com', substring(url,39)) 
  where createdby='gossip.cgi'

2. MySQL merge 2 tables

I have 2 MySql tables which have the same structure/indexes/unique index, each have tens of thousands data, and want to merge table2 into table1. The following is my way:

insert ignore into table1
  select * from table2;

I remember Oracle PL/SQL has the same syntax with MySQL for this kind of merge.

MySQL: a column with a unique index as well as a normal index

MySQL: a column with a unique index as well as a normal index

In MySQL, can a table field owns different indexes? Yes, in some case, we can do that to increase the performance.

I have a table ‘keywords’, and want to add 2 different indexes for keyword field in the table: 1 is for its unique (no duplicated keyword), 2 is for speeding up (in the case of input-field auto-complete). Like this:

alter table `keywords` add unique index (`keyword`);
ALTER TABLE  `keywords` ADD INDEX (`keyword`);

This table is used for search-form auto-complete. Originally it is only with unique index, and is slow when user inputs keyword. After adding the index on `keyword` for speed-up purpose, which runs ‘select keyword from keywords where keyword like ‘%’._GET[‘k’].’%‘, I checked the result and it becomes much faster indeed. So adding a normal index besides unique index works. The conclusion is With or without the normal index is quite different.

It is somewhat confused for hiring 2 indexes on the same field. However, it works in the real-world. Without the second normal index on `keyword`, it is pretty slow.

Having several indexes on one field are very useful in some case, essentially, they do index different things.

a MySQL procedure, and a tip

a MySQL procedure, and a tip

I wrote a simple MySQL stored-procedure to random set column value, it is interesting:

DELIMITER $$
create procedure cur_update()
begin
 declare a tinyint unsigned;
 declare b varchar(128);
 declare c int unsigned;

 declare curl2 CURSOR for select cid from contents 
   where updated > '2012-09-05';

 open curl2;

 LOOP
  fetch curl2 into c;
  select cid, name into a, b from categories order by rand() limit 1;  
  update contents ct 
    set ct.cate_id = a, ct.category = b
    where ct.cid = c;
end LOOP;

close curl2;
end$$

DELIMITER;

It works fine.
For a non-english database, if backup and recover, it might have problem without charset setting, should like this:


$ mysql -u root utf8_general_ci-db --default-character-set=utf8 < 
  utf8_general_ci-db.sql

PHP and MySQL tips

print array or scalar?

My version of print array or scalar on the screen like this:

function prints($vars) {
 global $config;
 if (!isset($config['debug']) || (! $config['debug']) ) return;
 if(is_array($vars) || is_object($vars)) {
  echo "<pre>"; print_r($vars); echo "</pre>";
 }
 else echo $vars."<br>\n";
}

Dynamic load Class
class_exists — Checks if the class has been defined

if (!class_exists("someClass", false)) {
 require_once "someClass.php";
}
or:
if (class_exists('MyClass')) {
    $myclass = new MyClass();
}

MySQL: Date functions

If you’re looking for an SQL query that returns the number of days, hours and minutes between date1 and now:

SELECT CONCAT(DAYOFYEAR(date1)-DAYOFYEAR(NOW()),' days ',
DATE_FORMAT(ADDTIME("2000-00-00 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(date1)-TIME_TO_SEC(NOW()))),'%k hours and %i minutes'))
 AS time FROM time_table;
+---------------------------------+
| time                            |
+---------------------------------+
| 27 days 2 hours and 52 minutes  |
+---------------------------------+

The following is from http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_last-day.

(1) I’m using this query for a birthday-reminder:

SELECT `geb_Geboorte`
FROM `gebruikers`
WHERE
DAYOFYEAR( curdate()) <= dayofyear( `geb_Geboorte` )
AND
DAYOFYEAR( curdate())+15 >= dayofyear( `geb_Geboorte` );

(2) Just another example on how to figure out how many days are until some birthdate (in order to do a range query, or get the “next” birthday):

SELECT name, birthday,
IF(DAYOFYEAR(birthday) >= DAYOFYEAR(NOW()),
DAYOFYEAR(birthday) - DAYOFYEAR(NOW()),
DAYOFYEAR(birthday) - DAYOFYEAR(NOW()) +
DAYOFYEAR(CONCAT(YEAR(NOW()),'-12-31')))
AS distance
FROM birthdates;

The + DAYOFYEAR(CONCAT(YEAR(NOW()),’-12-31′)) (which is 366 or 365, depending on whether we’re in a leap year or not) takes care of the New Year’s Eve wrap around.

(3) This is another query for the birthday remainder :

SELECT * FROM `users`
WHERE 
( 
  DAYOFYEAR( NOW() ) > DAYOFYEAR( DATE_SUB(birthdate,INTERVAL 7 DAY) ) 
 AND
  DAYOFYEAR( NOW() ) <= DAYOFYEAR( DATE_SUB(birthdate,INTERVAL 7 DAY) )+7 
)
OR
( 
  DAYOFYEAR( NOW() ) > DAYOFYEAR( birthdate )-7 
 AND 
  DAYOFYEAR( NOW() ) <= DAYOFYEAR( birthdate ) 
);

MySQL: 4 tips

(1) update 1 table with other table’s values:
Instead of using cursor, mysql has a convenient way to update a table (row by row) by other table’s value. The following sql is to replace table t’s passwd field with table h’s passwd field value.

update users_prod as t, (select * from users_org) as h set
t.passwd=h.passwd
where t.id=h.id
and t.passwd is null

This is an efficient way, using (select …) as alias to fetch data into memory, then loop the memory to do the update.

(2) load data with pre-process:
The following is a more powerful load table with pre-process function, to update columns while loading.

mysql> LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t
IGNORE 1 LINES
(@date,@time,@name,@weight_lb,@state)
SET
 dt = CONCAT(@date,' ',@time),
 firstname = SUBSTRING_INDEX(@name,' ',1),
 lastname = SUBSTRING_INDEX(@name,' ',-1),
 weight_kg = @weight_lb * .454,
 st_abbrev = (select abbrev from states where name = @state);

(3) create view:
create view can save a lot of codes and time to optimism application. The following are 2 examples: 1 is from Paul DuBois‘s book, 1 from http://dev.mysql.com/doc/refman/5.0/en/create-view.html:

 CREATE VIEW view_mail AS
  SELECT
  DATE_FORMAT(t, '%M %e, %Y') AS date_sent,
  CONCAT(from, '@', from_host) AS sender,
  CONCAT(to, '@', to_host) AS recipient, size
  FROM mail
create function book_subject
returns varchar(64) as
return @book_subject;
--
create view thematical_books as
select title, author
from books
where subject = book_subject();

MySQL: stored procedure and function

While writing MySQL functions, I found a helpful article from web and extracted it, put here for reference.

I always find it amusing that when people talk about database logic they are very focused on stored procedures and its almost like nothing else exists. Makes me wonder if these people have ever worked with modern databases. Stored procedures are one of the oldest methods of encapsulating database logic, but they are not the only method available. Many relational databases nowadays have views, constraints, referential integrity with cascading update, delete, stored functions, triggers and the like. These are extremely powerful tools when used appropriately.

In the next couple of sections we’ll cover stored procedures and these other kinds of database objects and detail the strengths and weaknesses of each for encapsulating logic. We will give a rating of 0-5 for each feature 0 meaning the feature is non-existent, 5 meaning this object is one of the best suited objects for implementing this kind of task.

Stored Procedures

Stored procedures are one of numerous mechanisms of encapsulating database logic in the database. They are similar to regular programming language procedures in that they take arguments, do something, and sometimes return results and sometimes even change the values of the arguments they take when arguments are declared as output parameters. You will find that they are very similar to stored functions in that they can return data; however stored procedures can not be used in queries. Since stored procedures have the mechanism of taking arguments declared as OUTPUT they can in theory return more than one output.

Feature Rating
Works in various kinds of databases 3 (many  databases such as DB II, Oracle, SQL Server, MySQL 5, PostGreSQL,
FireBird support them).  There are also a lot that don’t e.g.
MySQL < 5.0, MS Access (although parameterized queries serve a similar role)
Can be called by multiple applications and interfaces 4 (generally they can be called, but the use of OUTPUT arguments  is not
always usable)
Can take an undefined number of arguments 2 (note most databases allow to define optional arguments, but this can
become very unwieldy to maintain if there are a lot  because you end up
duplicating logic even within the stored procedure so is generally avoided)
Reusability within the database 3 (you can not reuse them in views, rarely in stored functions and other
stored procedures unless the stored procedure using it does not require a
return value or result query).  This varies slightly from DBMS to DBMS.
Can be used to change data in a table without giving rights to a user to
change table directly
4  In general true for most DBMSs
that support them.
Can return varying number of fields given different arguments. 3 –again in theory it can, but very hard to
maintain since you would often be duplicating logic to say return one field
in one situation and other set of fields in another situation or update a
field when the field is passed in as an argument. Note that in many databases such as for example SQL Server and Oracle, one can return multiple result sets with a stored procedure, but the receiving end needs to be able to do a next result set call and know the sequence in which the result sets are being sent.
Long stretches of SQL easy to read and maintain 5 (one of the great strengths of stored procedures is that you can have long transactions of sql statements and conditional loops which can be all committed at once or rolled back as a unit. This also saves on network traffic.

Stored Functions

Stored Functions are very similar to stored procedures except in 3 major
ways.

  1. Unlike
    stored procedures, they can be used in views, stored procedures, and other
    stored functions.
  2. In
    many databases they are prohibited from changing
    data or have ddl/dml limitations.  Note for databases such as PostGreSQL
    this is not true since the line between a stored function and a stored
    procedure is very greyed
  3. They
    generally can not take output arguments (placeholders) that are then passed back out with changed values.
Feature Rating
Works in various kinds of databases 3 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 5, PostGreSQL).  There are also a lot that don’t e.g.
MySQL < 5.0, MS Access
Can be called by multiple applications and interfaces 4 (generally they can be called, but the use of OUTPUT arguments  is not
always usable)
Can take an undefined number of arguments 2 (note most databases allow to define optional arguments, but this can
become very unwieldy to maintain if there are a lot  because you end up
duplicating logic even within the stored function so is generally avoided)
Reusability within the database 5 (you can reuse them in views, in other stored functions and
stored procedures).  This varies slightly from DBMS to DBMS.
Can be used to change data in a table without giving rights to a user to
change table directly
3  Many databases do not allow changing of data in stored functions except temp table data, but those that do in general support this.
Can return varying number of fields given different arguments. 4 –For databases such as SQL Server, PostgreSQL, DB 2, Oracle that allow return tables and sets, you can selectively pick fields you want from within a query. So although the function always outputs the same number of fields, you can selectively use only some similar to what you can do with views. This is not true for scalar functions (MySQL 5.1- only supports scalar functions).
Long stretches of SQL easy to read 5 – yes – you can do fairly intensive multi-line processing which in the end returns one value or table to the user.

Views

Views are one of the greatest things invented since sliced bread.
The main beauty of a view is that it can be used like a table in most situations, but unlike a table, it can encapsulate very complex calculations and commonly used joins. It can also use pretty much any object in the db except for stored procedures. Views are most useful when you always need to join the same set of tables say an Order with an Order Detail to get summary calculation fields etc.

Feature Rating
Works in various kinds of databases 4 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 5, PostGreSQL, SQLite, MSAccess (calls it a query)).  There are also some that don’t e.g.
MySQL < 5.0
Can be called by multiple applications and interfaces 5 (generally they can be called anywhere where you can call a table which is pretty much everywhere)
Can take an undefined number of arguments 5 (you can select subsets of columns and rows from a view just like you can from a table)
Reusability within the database 5 (you can reuse them in other views, in stored functions and
stored procedures).
Can be used to change data in a table without giving rights to a user to
change table directly.
3  In many databases Views are read-only and complex views are rarely updateable. Note that for example some databases such as Oracle,PostgreSQL, MS SQL Server , SQLite
will allow you to update even a complex view by using an instead of trigger or rule against the view. MySQL 5, MS SQL Server and some others automatically make one table views updateable. MS Access has a fairly sophisticated update algorithm for queries in that it automatically makes one table and even multi-table queries updateable and allows deletes if you define primary keys appropriately. Also depending on the field a column comes from it will update that and also automatically create child records in child tables if you try to update a child column when there is no record.
Can return varying number of fields given different arguments. 4 – (you can select subsets of columns and rows from a view just like you can from a table). However you can’t change the underlying structure e.g. return records from a different set of tables like you can with a stored procedure or function.
Long stretches of SQL easy to read. 3 (A view can often be defined with an administrative designer or using a color coded sql editor so is fairly easy to read) – in general though it is limited to only one select statement or unioned select statements.

Triggers And Rules

Triggers are objects generally tied to a table or view that run code based on certain events such as inserting data, before inserting data, updating/deleting data and before these events happen.

Triggers can be very great things and very dangerous things. Dangerous in the sense that they are tricky to debug, but powerful because no update to a table with a trigger can easily escape the trigger.

They are useful for making sure certain events always happen when data is inserted or updated – e.g. set complex default values of fields, inserting logging records into other tables.

Triggers are especially useful for one particular situation and that is for implementing instead of logic. For example as we said earlier, many views involving more than one table are not updateable. However in DBMS such as PostgreSQL, you can define a rule on a view that occurs when someone tries to update or insert into the view and will occur instead of the insert. The rule can be fairly complex and can layout how the tables should be updated in such a situation. MS SQL Server and SQLite let you do something similar with INSTEAD OF triggers.
Note the term Rule is a little confusing in DBMS because they mean quite different things. In Microsoft SQL Server for example a Rule is an obsolete construct that was used to define constraints on tables. In PostgreSQL a Rule is very similar to a trigger except that it does not get triggered per row event and is defined without need of a handling function.

Feature Rating
Works in various kinds of databases 2 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 5, PostGreSQL,).  There are lots that don’t e.g.
MySQL < 5.0, MySQL 5 limited, MS Access
Can be called by multiple applications and interfaces 5 (it just happens behind the scenes. No application can escape them)
Can take an undefined number of arguments 0 ( strictly for updating data and against a table or view )
Reusability within the database 0 – No
Can be used to change data in a table without giving rights to a user to
change table directly.
4  In general yes for databases that support them
Can return varying number of fields given different arguments. 0 –Triggers are strictly for updating data
Long stretches of SQL easy to read. A trigger can often be defined with an administrative designer or using a color coded sql editor so is fairly easy to read 5

Foreign Key Constraints, Primary Key Constraints, Referential Integrity, Cascade Update/Delete

No true database should be without Constraints, Referential Integrity, and Cascade Update/Delete. You can define them with SQL DDL statements or using a relational designer. The use of these tools are limited but the purpose they serve can not be easily replicated with other database objects.
These tools serve 2 main purposes.

  • Provide a declarative model of the database and how the data is related to each other. A self-documenting tool.
  • Insure that you don’t mistakenly do something stupid in your coding to violate your model. If your model is sound, errors in your code signal a flaw in your programming logic. If you get errors in your programming logic, then verify that your model is sound.
Feature Rating
Works in various kinds of databases 3 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 4+ (4 is limited, 3 very limited and varies depending on storage engine (InnoDB vs MyISAM)), PostGreSQL, MS Access).  There are lots that don’t e.g.
MySQL < 5.0
Can be called by multiple applications and interfaces 5 (it just happens behind the scenes. No application can escape them)
Can take an undefined number of arguments 0 (they take no arguments )
Reusability within the database 5 – Yes – no matter where your updates, inserts occur, they can not escape the iron-fist of the Key Constraints and Cascade Update/Delete rules next to disabling them.
Can be used to change data in a table without giving rights to a user to
change table directly.
4  Really only applies to cascade update/delete rules Yes – cascade update/delete rules are a special kind of trigger so to speak that kick in when a potential referential integrity constraint can be violated. Example if you update a key field, a cascade update on the foreign keys will force an update on the foreign key field to correct the situation so you don’t end up with orphan data.
Can return varying number of fields given different arguments. 0 –not relevant
Long stretches of SQL easy to read. A trigger can often be defined with an administrative designer or using a color coded sql editor so is fairly easy to read 0 – not relevant

Dynamically Generated SQL

In this section we discuss the pros and cons of using dynamically generated sql vs. only using stored procedures, view, etc.

Feature Rating
Works in various kinds of databases 4 (guaranteed to work for any database, although the syntax may vary from database.)
Can be called by multiple applications and interfaces 3 (Will only work in the application where the dynamic sql is defined or if the application is wrapped as a shared library. The use of shared library is often limited as to where you can use it. However your logic can be applied to multiple databases without needing to alter the db by adding stored procs etc. if you are always using the same app to access these databases.)
Can take an undefined number of arguments 5 (you can select subsets of columns and rows from tables, change tables you are reading from etc.)
Reusability within the database 2 (non-existent except in some very rare situations and databases that allow you to load shared libraries).
Can be used to change data in a table without giving rights to a user to
change table directly.
4  You can have the application control the security so in that sense the application only needs to have rights
Can return varying number of fields given different arguments. 5 – (you can select subsets of columns and rows, do complex joins ). You can change the underlying structure e.g. return records from a different set of tables.
Long stretches of SQL easy to read. 2 – SQL often sits interspersed with other programming logic which makes it somewhat difficult to read depending on the application language and also difficult to write complex transactional sql.

What about Security?

In the above criteria, we didn’t put Security in as a feature/benefit even though many people will argue that dynamic SQL and the like are not secure.
This actually varies depending on how you implement Dynamic SQL, e.g. if you are using an O/R Mapper or built-in dataadapters (supported in .NET),prepared statements, or are very careful to sanitize inputs you are using in your sql then
your Dynamic SQL will probably be just as secure or sometimes more so than using stored procedures since some people are of the mindset that stored procedures are a magic bullet for protecting against SQL Injection and other attacks (such as script injection) regardless of how they are implemented and called and thus neglect other necessary precautions.

MySQL: ‘select count(*)’ is much faster than ‘select *’

If we want to get the number of table records, normally there are 2 ways to do so:

  1. select * from table
  2. select count(*) from table

Examples:
(1). use ‘select * from table’

$num_rows=array();
$queries = array('select * from a', 'select * from b', ...);
foreach ($queries as $sql) {
  $result = mysql_query($sql, $link);
  $num_rows[] = mysql_num_rows($result);
  mysql_free_result($result);
}
 // use $num_rows[0], $num_rows[1] to get the table number.

(2). use ‘select count(*) from table’

$num_rows=array();
$queries = array('select count(*) from a', 'select count(*) from b', ..);
 foreach ($queries as $sql) {
  $result = mysql_query($sql, $link);
  $num_rows[] = mysql_fetch_row($result);
  mysql_free_result($result);
 }
// $num_rows[0][0],$num_rows[1][0] instead of $num_rows[0], $num_rows[1]...

The above 2 are not the same, with very big difference.
I found ‘select count(*) from table’ (2) is much faster than ‘select * from table’ (1). For total 4 tables, which records from 1000 to 260000, the speed is 2.78S vs. 32ms. The (2) is 9 times quicker than (1).

The (1) fetch all records and use mysql_num_rows to sum up the total number, eats a lot of memory.
The (2) just get the total number of the table, no table data touched, so save huge memory.
And, (1) is to search table itself; while (2) is to search table index, so 2 is much quicker.

So, for a indexed table, it is a better choice to always use (2) ‘select count(*) from table’ to get the table number.

MySQL: 'select count(*)' is much faster than 'select *'

If we want to get the number of table records, normally there are 2 ways to do so:

  1. select * from table
  2. select count(*) from table

Examples:
(1). use ‘select * from table’

$num_rows=array();
$queries = array('select * from a', 'select * from b', ...);
foreach ($queries as $sql) {
  $result = mysql_query($sql, $link);
  $num_rows[] = mysql_num_rows($result);
  mysql_free_result($result);
}
 // use $num_rows[0], $num_rows[1] to get the table number.

(2). use ‘select count(*) from table’

$num_rows=array();
$queries = array('select count(*) from a', 'select count(*) from b', ..);
 foreach ($queries as $sql) {
  $result = mysql_query($sql, $link);
  $num_rows[] = mysql_fetch_row($result);
  mysql_free_result($result);
 }
// $num_rows[0][0],$num_rows[1][0] instead of $num_rows[0], $num_rows[1]...

The above 2 are not the same, with very big difference.
I found ‘select count(*) from table’ (2) is much faster than ‘select * from table’ (1). For total 4 tables, which records from 1000 to 260000, the speed is 2.78S vs. 32ms. The (2) is 9 times quicker than (1).

The (1) fetch all records and use mysql_num_rows to sum up the total number, eats a lot of memory.
The (2) just get the total number of the table, no table data touched, so save huge memory.
And, (1) is to search table itself; while (2) is to search table index, so 2 is much quicker.

So, for a indexed table, it is a better choice to always use (2) ‘select count(*) from table’ to get the table number.

2 tips from work

PHP: header() can’t redirect

I added a login-check at the top of php:

session_start();
if( !isset($_SESSION['userid']) || empty($_SESSION['userid']) ) {
	header("Location: " . LOGIN);
	exit;
}

It didn’t work. Warning: Cannot modify header information – headers already sent by (output started at /home/william/test.php:5)

Since the codes use several class inheritances from different phps, so it is a little difficult to find where actually the problem is. Finally, I found the reason — very weird — it is caused by some spaces after the class definition :

class SmartyExtendBase extends Smarty {
  var $db1, $db2;
  function __construct() {
    parent::__construct();
    ...
  }
}spaces here......

So make sure the codes are clear and tidy: no more spaces in un-necessary place. A remedy checker in Linux is to use the following to display hidden/special chars:
$ cat -A test.php

MySQL: control 2 columns of timestamp type

When designing MySQL table, it is very common to have both created time and updated time cloumns: 1 for record created, 1 for record updated.

I use the following, works fine; it seems a good way to simplify both create and update operation:

create table test(...
  `createdby` varchar(50) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT '2011-04-00 00:00:00',
  `updatedby` varchar(50) DEFAULT NULL,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 );

According to MySQL timestamp, MySQL requires code doing the insert explicitly set a value for the created / inserted timestamp column, and no way to handle this implicitly.
So after the table created, we only care insert section: insert into test(…, created) values(…, now());
for update, no need to explicit, implicit works for ‘update’.

The following are some related helpful info.

1. For a single timestamp column, the create/update is simple and implicit:

  • Auto-initialization and auto-update:
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  • Auto-initialization only:
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  • Auto-update only:
    ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
  • Neither:
    ts TIMESTAMP DEFAULT 0

2. To specify automatic default or updating for a TIMESTAMP column other than the first one, you must suppress the automatic initialization and update behaviours for the first TIMESTAMP column by explicitly assigning it a constant DEFAULT value (for example, DEFAULT 0 or DEFAULT ‘2003-01-01 00:00:00’). Then, for the other TIMESTAMP column, the rules are the same as for the first TIMESTAMP column, except that if you omit both of the DEFAULT and ON UPDATE clauses, no automatic initialization or updating occurs. Like:

CREATE TABLE t (
  ts1 TIMESTAMP DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP);

3. The order of the DEFAULT and ON UPDATE attributes does not matter. If both DEFAULT and ON UPDATE are specified for a TIMESTAMP column, either can precede the other. For example, these statements are equivalent:

CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   DEFAULT CURRENT_TIMESTAMP);