您可以通过Cloud Spanner API检查表是否存在?(在node.js中)

Question

我如何检测表是否存在或不在数据库中？没有：

database.exist('some table name')

我写我的功能：

const queryTableExist = (tableName) => {
    return {
        sql: `SELECT t.table_name FROM information_schema.tables AS t WHERE t.table_catalog = '' and t.table_schema = '' and  t.table_name='${tableName}'`
    };
};
let tableExist = (tableName, cb) => {
    const query = queryTableExist(tableName);
    database.run(query, (err, rows) => {
        if (err) {
            console.log(`${err}n${query}`);
            cb(err);
        }
        cb(err, rows.length > 0);
    })
};

还有其他想法吗？

Answer 1

假设您使用的是Google-cloud节点client库和云跨度软件包：

您可以致电call table.create（）并处理错误：

var schema =
  'CREATE TABLE Singers (' +
  '  SingerId INT64 NOT NULL,' +
  '  FirstName STRING(1024),' +
  '  LastName STRING(1024),' +
  '  SingerInfo BYTES(MAX),' +
  ') PRIMARY KEY(SingerId)';
table.create(schema, function(err, table, operation, apiResponse) {
  if (err) {
    // Error handling omitted.
  }
  operation
    .on('error', function(err) {})
    .on('complete', function() {
      // Table created successfully.
    });
});

或者您可以尝试获取对表的引用，然后检查一个空对象：

var instance = spanner.instance('my-instance');
var database = instance.database('my-database');
var table = database.table('my-table');

Answer 2

Google Spanner API当前不支持exists函数来发现存在哪个表。因此，您目前需要自己查询。

获得答案的最高性能

获取所有存在的表

SELECT
    t.table_name
FROM
    information_schema.tables AS t
WHERE
    t.table_catalog = '' AND t.table_schema = ''

找出是否存在某些表格

SELECT
    t.table_name
FROM
    information_schema.tables AS t
WHERE
    t.table_catalog = '' AND t.table_schema = '' AND
    t.table_name = 'yourTableNameA' OR
    t.table_name = 'yourTableNameB' OR
    t.table_name = 'yourTableNameC'

Answer 3

我认为最好的方法是从数据库请求DDL，并检查带有正则表达式的CREATE TABLE <your-table-name-here> ( ...语句：

var instance = spanner.instance('my-instance');
var database = instance.database('my-database');
function databaseHasTable(database, tableName) {
  return database.getSchema().then(function (data) {
    var statements = data[0];
    var matcher = new RegExp('^create table ' + tableName + ' \(', 'i');
    var results = statements.filter(function (item) {
      return matcher.test(item);
    });
    return results.length == 1;
  });
}
// This should print out true or false depending on the table being there.
databaseHasTable(database, 'my-table').then(console.log);