dreamluverz collection
Let’s dream together

MYSQL: FIND_IN_SET AND IN()

Using IN() for comma delimited and my query is something like SELECT * FROM TABLE WHERE FLD IN(’33, 148′)
TABLE

(ID) (FLD) COMMA DELIMITED FIELD
1 42, 148
2 234, 25423,148

It wont be able to retrieve any records at all coz it will only check for the exact values inside that quote ‘33, 148′. Even I put something like SELECT * FROM TABLE WHERE FLD IN(’148′) still won’t have any result coz I don’t have a record that’s only ‘148′

So instead I used something like SELECT * FROM TABLE WHERE FIND_IN_SET( ‘148′, FLD) and these 2 rows will be retrieved. So I guess IN() is not a good way to retrieve fields with comma delimited values unless it’s the exact string. But if ur sql is like this SELECT * FROM TABLE WHERE ID IN(1,2), it won’t be a prob at all.

Btw. I tried the issue below and it’s not a prob anymore. I only used the sql like SELECT * FROM TABLE WHERE FIND_IN_SET( ‘1487′, FLD) and it only retrieved 1487 .

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

I use FIND_IN_SET() to return results containing a certain number in a comma-delimmeted

list in a TEXT field.  Sometimes it returns false positives, 

for example:mysql> SELECT pssmID_list FROM bccs WHERE FIND_IN_SET(1487, pssmID_list);

More »

Tags: bug, find_in_set, in(), mysql

IN()

At first I thought this is not possible So I just gave it a try and it works

update TABLE set field=’s’ WHERE id IN (’1′, ‘2′)

Tags: in(), mysql, sql

PLESK - how to create remote access on mysql

create a user for  any host or %. And you will be able to access mysql remotely.

Tags: mysql, plesk, remote access

FROM_UNIXTIME - to convert timestamp to string date in mysql

I’ve been looking for a solution on how to convert timestamp to string in my query. I’ve tried using DATE_FORMAT but it didn’t work coz my dates are saved as timestamp. After long search I found this… FROM_UNIXTIME… and it solved my problem

select FROM_UNIXTIME(1202635768, ‘%Y%m’)  from table

=> will return 200802

Tags: convert timestamp to string date, FROM_UNIXTIME, mysql

SQL_CALC_FOUND_ROWS

One of my teammates suggested this to use in our paging class. And just wanted to share it with you.

soource: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

FOUND_ROWS()

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

In the absence of the SQL_CALC_FOUND_ROWS option in the most recent SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement.

The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to refer to the value later, save it:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ;
mysql> SET @rows = FOUND_ROWS();

If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.

SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result.

The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION statements than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole.

The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use of SQL_CALC_FOUND_ROWS with UNION are:

• The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.
• The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.
• If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION.

Important

FOUND_ROWS() is not replicated reliably, and should not be used with databases that are to be replicated.

 I also wanted to include this for your reference:

Posted by Wade Bowmer on May 14 2006 11:40pm

Be aware that using SQL_CALC_FOUND_ROWS and FOUND_ROWS() disables ORDER BY … LIMIT optimizations (see bugs http://bugs.mysql.com/bug.php?id=18454 and http://bugs.mysql.com/bug.php?id=19553). Until it’s fixed, you should run your own benchmarks with and without it.

Tags: found_rows(), mysql, SQL_CALC_FOUND_ROWS