


How to solve the over-memory problem of using mysql_query to query very large result sets in PHP, phpmysql_query_PHP tutorial
How to solve the over-memory problem of PHP using mysql_query to query very large result sets? phpmysql_query
When using mysql_query to query very large result sets, a fatal error of exceeding the memory limit will occur. This is because mysql_query uses the method of querying all results and then caching the entire result set into memory.
Mysql query also provides another query method. The function name is mysql_unbuffered_query. This function uses the result set immediately after finding the result, and does not cache the result set into the memory, thus avoiding exceeding the limit. A memory situation occurs. However, the cost of using this method is that you cannot use methods such as getting the head office when querying, because this method returns results while querying. At the same time, you cannot perform other operations on the same database link when using this method. If you want to perform other operations, you must first terminate the current operation, release all result rows generated by uncached SQL queries, or re-instantiate a database. Connect and use the new link for other actions.
The following is a comparison between using cache and not using cache (there are more than 10 million rows of data in the queried table):
function selecttest() { try { $pdo = new PDO("mysql:host=localhost;dbname=test", 'root', '123456'); // 不使用缓存结果集方式 // $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); $sth = $pdo->prepare('select * from test'); $sth->execute(); echo '最初占用内存大小:' . memory_get_usage() . "\n"; $i = 0; while ($result = $sth->fetch(PDO::FETCH_ASSOC)) { $i += 1; if ($i > 10) { break; } sleep(1); print_r($result); echo '占用内存大小:' . memory_get_usage() . "\n"; } } catch (Exception $e) { echo $e->getMessage(); } }
The method used above is to cache all result sets. When running this function, an out-of-memory error will be reported, as shown below:
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 204800000 bytes) in E:ProgramDevelopmentRuntimeEnvironmentxampphtdocstesttest.php on line 57
Call Stack:
0.0005 135392 1. {main}() E:ProgramDevelopmentRuntimeEnvironmentxampphtdocstesttest.php:0
0.0005 135568 2. test->selecttest() E:ProgramDevelopmentRuntimeEnvironmentxampphtdocstesttest.php:86
0.0055 142528 3. PDOStatement->execute() E:ProgramDevelopmentRuntimeEnvironmentxampphtdocstesttest.php:57
Memory limit exceeded while executing $sth->execute();;
Removing the comment from // $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); will use the method of not caching the result set. Running this function will output the following content:
Initial memory size: 144808
Array ( [id] => 1 [a] => v [b] => w [c] => i )
Memory size occupied: 145544
Array ( [id] => 2 [a] => b [b] => l [c] => q )
Memory size occupied: 145544
Array ( [id] => 3 [a] => m [b] => p [c] => h )
Memory size occupied: 145536
Array ( [id] => 4 [a] => j [b] => i [c] => b )
Memory size occupied: 145536
Array ( [id] => 5 [a] => q [b] => g [c] => g )
Memory size occupied: 145536
As you can see, the memory occupied by obtaining a row of results without caching the result set is very small. This solves the problem of exceeding the memory limit.
Articles you may be interested in:
- PHP Mysql jQuery implements jQuery for publishing Weibo program
- PHP Mysql jQuery implements dynamic display of information
- jQuery combination PHP MySQL implements secondary linkage drop-down list [Example]
- Resolving PHP mysql_query execution timeout (Fatal error: Maximum execution time…)
- The difference between PHP’s mysqli_query parameters MYSQLI_STORE_RESULT and MYSQLI_USE_RESULT
- Implementation of red and blue (upvote) voting code based on PHP jQuery MySql
- JQuery Ajax PHP Mysql implementation of paging display data example explanation

To protect the application from session-related XSS attacks, the following measures are required: 1. Set the HttpOnly and Secure flags to protect the session cookies. 2. Export codes for all user inputs. 3. Implement content security policy (CSP) to limit script sources. Through these policies, session-related XSS attacks can be effectively protected and user data can be ensured.

Methods to optimize PHP session performance include: 1. Delay session start, 2. Use database to store sessions, 3. Compress session data, 4. Manage session life cycle, and 5. Implement session sharing. These strategies can significantly improve the efficiency of applications in high concurrency environments.

Thesession.gc_maxlifetimesettinginPHPdeterminesthelifespanofsessiondata,setinseconds.1)It'sconfiguredinphp.iniorviaini_set().2)Abalanceisneededtoavoidperformanceissuesandunexpectedlogouts.3)PHP'sgarbagecollectionisprobabilistic,influencedbygc_probabi

In PHP, you can use the session_name() function to configure the session name. The specific steps are as follows: 1. Use the session_name() function to set the session name, such as session_name("my_session"). 2. After setting the session name, call session_start() to start the session. Configuring session names can avoid session data conflicts between multiple applications and enhance security, but pay attention to the uniqueness, security, length and setting timing of session names.

The session ID should be regenerated regularly at login, before sensitive operations, and every 30 minutes. 1. Regenerate the session ID when logging in to prevent session fixed attacks. 2. Regenerate before sensitive operations to improve safety. 3. Regular regeneration reduces long-term utilization risks, but the user experience needs to be weighed.

Setting session cookie parameters in PHP can be achieved through the session_set_cookie_params() function. 1) Use this function to set parameters, such as expiration time, path, domain name, security flag, etc.; 2) Call session_start() to make the parameters take effect; 3) Dynamically adjust parameters according to needs, such as user login status; 4) Pay attention to setting secure and httponly flags to improve security.

The main purpose of using sessions in PHP is to maintain the status of the user between different pages. 1) The session is started through the session_start() function, creating a unique session ID and storing it in the user cookie. 2) Session data is saved on the server, allowing data to be passed between different requests, such as login status and shopping cart content.

How to share a session between subdomains? Implemented by setting session cookies for common domain names. 1. Set the domain of the session cookie to .example.com on the server side. 2. Choose the appropriate session storage method, such as memory, database or distributed cache. 3. Pass the session ID through cookies, and the server retrieves and updates the session data based on the ID.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

Zend Studio 13.0.1
Powerful PHP integrated development environment

SublimeText3 English version
Recommended: Win version, supports code prompts!