Things which can be done with MYSQL instead of involving PHP

Question

What are the must-know MYSQL functions (like IF etc..,) which every web developer should know?

Answer 1

My favorite:

INSERT ... SELECT

It allows me to bypass heavy PHP code to fetch data from one table, manipulate it and insert into another.

Answer 2

My personal favorite is "insert... on duplicate key update..." ;-)

Every string functions: concat, concat_ws, ... now().

GROUP BY functions: sum(), max(), count() + the wonderful group_concat()

JOIN clauses and VIEWs if you you are planning complex requests.

Well, in fact, the more you know MySQL (I mean, everything), the best.

Answer 3

i'd say:

CREATE VIEW .. AS SELECT

when i have querys that i use all the time, but it's not a function =P

Answer 4

Triggers and stored procedures offer much automation that involve only the SQL engine and this will significantly reduce the Web server's load and PHP code involved in data housekeeping.

Answer 5

My favorite:

alter table child add Foreign key (parent_id) references parent(id);

Taking care of data integrity with foreign keys will reduce your code base a lot. Not mentionign MySQL is better at this than PHP.

Answer 6

Some features that come to my mind:

• UNION and UNION ALL if your Model involves tables with similar data:

  (SELECT ...) UNION ALL (SELECT ...)

• IFNULL and NULLIF

• RLIKE for regular expression matching

• If you work with fulltext indexes:

  ... WHERE MATCH (field) AGAINST ('string')

  (Also take a look at the boolean matching)

• Memory tables

• INSERT DELAYED