Node.js的mysql/mysql2驱动获取Advisory Lock返回null是否正常？

Alright, let's figure out why this is happening. You said running SELECT GET_LOCK('test2', -1) directly in the MySQL client gives you the expected 0 or 1, but using the mysql1 library in Node.js returns null—that's definitely not right, and here's what's going on:

First, check how you're accessing the result #

In your code, the multiQuery function returns the full result array from pool.query(). For a single-row SELECT like this, the actual value you want is nested inside that array. Try modifying your test to log response[0].test3 instead of just response—while this might not fix the null issue itself, it ensures you're looking at the correct data point.

The real culprit: type casting in the mysql1 library #

Chances are, the mysql1 package (or maybe a typo for the older, deprecated mysql v1.x library) has default type parsing rules that mess up the return value of GET_LOCK().

GET_LOCK() returns a TINYINT(1) type, and some older MySQL libraries automatically cast TINYINT(1) values to booleans. But even that should give you true/false, not null—so there's likely a bug in how the library handles this specific function's output.

Fixes to try: #

• Override type casting in your pool config
  Add a custom typeCast function to your pool setup to force TINYINT(1) values to be returned as numbers instead of being misinterpreted:

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  port: 3306,
  database: 'test',
  password: '',
  typeCast: (field, next) => {
    if (field.type === 'TINY' && field.length === 1) {
      // Convert the string value to a number
      return parseInt(field.string(), 10);
    }
    // Let the library handle other types normally
    return next();
  }
})

• Switch to a maintained library (mysql2)
  The original mysql library is deprecated, and mysql1 isn't widely supported. mysql2 is the actively maintained successor, with better type handling out of the box. It's almost a drop-in replacement for your code:
  1. Install it: npm install mysql2
  2. Update your import: import mysql from 'mysql2'

This should resolve the null issue immediately without needing custom type casting.

Verify the fix #

After making either change, run your test again. You should start seeing the expected 0 or 1 response from GET_LOCK() instead of null.

内容的提问来源于stack exchange，提问作者scull7