|
MySql 4.1 A Few Important Features To Consider
Author: HumanX | Tuesday September 23, 2003
MySql 4 development tree introduces new features to mysql that have long been needed. In this article I will only focus on the group_concat command and the --old-passwords boot option.
Connectivity
--old-passwords boot option with MySql gives you the ability to run mysql with clients that only supported MySql 4 and below. MySql 4.1 introduces a more complex security facility which did not allow applications with client libraries older the 4.1 to coexist.
--old-passwords is required during boot time and can be passed at a comma line argument or added to you my.cnf or my.ini file. --old-passwords does allow you to take advantage of the new password mechanisms while allowing older clients to connect to the database. (And yes php developers, this means PHP 4.3 and below can connect to Mysql without upgrading to PHP 5 development tree)
Concatenation data sets with GROUP_CONCAT
The day has finally arrived in which you can now concatenate your data sets into a single field. This single function has been badly needed by the web development or form development community.
GROUP_CONCAT allows you to focus on a single column and merge all data from your result set into a comma delimted list in a single field or you can use any of the concat features that are normally found in mysql to modify the format of the output data.
Example, let's say I have two tables that I would like to join together.
TABLE A
| ID | DATA
| 1 | anything
TABLE B
| ID | DATA
| 1 | jed
| 1 | tim
| 3 | sue
| 1 | edgar
Example Join
SELECT a.id, group_concat(DISTINCT b.data) from a
INNER JOIN a ON b.id=a.id
GROUP BY a.id
RESULT
| ID | DATA
| 1 | jed,tim,edgar
WITHOUT group_concat
| ID | DATA
| 1 | jed
| 1 | tim
| 1 | edgar
For me this has alot of advantageous from a PHP point of view:
1. Performance: Allow MySql to deal with parsing data instead of a scripting language which is much slower.
2. DATA Passing, Urls : Now I would not have to convert data from a MySql result source to page variables in a row.
3. Loop Statement : Once again, this allows me to drop many foreach while do statements that were needed in the past.
The group_concat statement can help limit coding in general especially from the perspective of a web developer.
Give mysql 4.1 a try, with the --old-passwords option you do not have to worry about backwards compatibility and you also have the opportunity to use many of MySql’s new security features as well as features such as group_concat.
Keywords
merge fields, merge results, merge columns, mysql, php, configuration
HumanX
LinuxDig.Com
|
