精华内容
下载资源
问答
  • b) I see a speed problem, using LIKE would I guess take more mysql take than "=" using the first approach where I exact match a word. On the other hand in the first option I need 2 statements, one to...

    I am making a synonyms list that I will store it in database and retrieve it before doing full text search.

    When users enters like: word1

    I need to lookup for this word in my synonyms table. So if the word is found, I would SELECT all the synonyms of this word and use it in the fulltext search on the next query where I contruct the query like

    MATCH (columnname) AGAINST ((word1a word1b word1c) IN BOOLEAN MODE)

    So how do I store the synonyms in a table? I found 2 choices:

    using key and word columns like

    val keyword

    -------------

    1 word1a

    1 word1b

    1 word1c

    2 word2a

    2 word2b

    3 word3a

    etc.

    So then I can find exact match of the entered word in one query and find it's ID. In the next select I get all the words with that ID and somehow concate them using a recordset loop in server side langauge. I can then construct the real search on the main table that I need to look for the words.

    using only word columns like

    word1a|word1b|word1c

    word2a|word2b|word2c

    word3a

    Now I so the SELECT for my word if it is inside any record, if it is, extract all the record and explode it at | and I have my words again that I can use.

    This second approach lookes easier to maintain for the one who would make this database of synonyms, but I see 2 problems:

    a) How do I find in mysql if a word is inside the string? I can not LIKE 'word1a' it because synonims can be very alike in a way word1a could be strowberry and strowberries could be birds and word 2a could be berry. Obviously I need exact match, so how could a LIKE statement exact match inside a string?

    b) I see a speed problem, using LIKE would I guess take more mysql take than "=" using the first approach where I exact match a word. On the other hand in the first option I need 2 statements, one to get the ID of the word and second to get all the words with this ID.

    How would you solve this problem, more of a dilemma which approach to take? Is there a third way I don't see that is easy for admin to add/edit synonyms and in the same time fast and optimal? Ok I know there is no best way usually ;-)

    UPDATE: The solution to use two tables one for master word and second for the synonym words will not work in my case. Because I don't have a MASTER word that user types in search field. He can type any of the synonyms in the field, so I am still wondering how to set this tables as I don't have master words that I would have ID's in one table and synonims with ID of the master in second table. There is no master word.

    解决方案

    Don't use a (one) string to store different entries.

    In other words: Build a word table (word_ID,word) and a synonym table (word_ID,synonym_ID) then add the word to the word table and one entry per synonym to the synonyms table.

    UPDATE (added 3rd synonym)

    Your word table must contain every word (ALL), your synonym table only holds pointers to synonyms (not a single word!) ..

    If you had three words: A, B and C, that are synonyms, your DB would be

    WORD_TABLE SYNONYM_TABLE

    ID | WORD W_ID | S_ID

    ---+----- -----+-------

    1 | A 1 | 2

    2 | B 2 | 1

    3 | C 1 | 3

    3 | 1

    2 | 3

    3 | 2

    Don't be afraid of the many entries in the SYNONYM_TABLE, they will be managed by the computer and are needed to reflect the existing relations between the words.

    2nd approach

    You might also be tempted (I don't think you should!) to go with one table that has separate fields for word and a list of synonyms (or IDs) (word_id,word,synonym_list). Beware that that is contrary to the way a relational DB works (one field, one fact).

    展开全文
  • ElasticSearch配合mysql实现同义词热更新 使用环境 elasticsearch6.1.2 mysql 同义词插件6.1.4 同义词插件下载地址 安装插件 地址: https://github.com/bells/elasticsearch-analysis-dynamic-synonym *下载插件时...

    ElasticSearch配合mysql实现同义词热更新

    第一次写博客,elasticsearch网上的资料实在太少了,在网上翻了很多资料,自己在做的时候,由于版本原因,插件集成出现了很多问题,在这里我总结一下,希望对大家有帮助,谢谢支持

    使用环境
    elasticsearch6.1.2
    mysql
    同义词插件6.1.4 同义词插件下载地址

    安装插件
    地址: https://github.com/bells/elasticsearch-analysis-dynamic-synonym
    *下载插件时,必须选择比较接近的版本下载,版本相差太多,集成插件会出现不兼容问题
    比如我使用的版本为6.1.2,我下载的是6.1.4版本的

    作者提供的插件包版本比较少,要想获得对应的插件包,则需要自己编译
    在这里插入图片描述

    编译说明
    1.打开plugin/DynamicSynonymPlugin.java将第40行 @Override 注释掉即可
    vim /elasticsearch-analysis-dynamic-synonym-master/src/main/java/com/bellszhu/elasticsearch/plugin/DynamicSynonymPlugin.java
    2.修改pom.xml文件,将6.1.4全部修改为6.1.2
    3.最后切换到elasticsearch-analysis-dynamic-synonym-master目录,执行mvn clean package编译打包
    *如果没安装maven环境,则需要先安装,并且使用阿里云国内镜像
    http://blog.csdn.net/fenglailea/article/details/53304479
    编译成功之后: 编译成功后文件位置:elasticsearch-analysis-dynamic-synonym-master/target/releases/elasticsearch-analysis-dynamic-synonym-6.1.2.zip
    4.将插件包移动到 mv /elasticsearch-6.1.2/plugins/dynamic-synonym 进行解压,并重启es

    插件支持两种方式,远程静态文件和远程访问接口
    远程访问接口
    注意:
    1.在数据库中创建同义词表
    在这里插入图片描述
    2.编写查询同义词接口
    说明:
    * 这个http请求需要返回两个头部,一个是 Last-Modified,一个是 ETag,只要有一个发生变化,该插件就会去获取新的同义词来更新相应的同义词。
    只要 PHP 返回的 ETAG 是 weak ETAG,那么就一切都会正常起来了。而所谓的 weak ETAG,也就是弱 ETAG,它是相对于正常 ETAG 而言的,表现形式就是 ETAG 前面加上 W/注:nginx 在开启了 gzip 之后,如果有 ETAG 则会调用 ngx_http_clear_etag 将其清除,解决的办法很简单:
    W/“db8b38e8a3257a2f195b727eceb2c5d3”

    /*
    * 查询跟踪任务列表
    * */
    @RequestMapping(value = "/synonym",produces="application/json;charset=utf-8")
    @ResponseBody
    public String list(HttpServletResponse response,
                     HttpServletRequest request) {
        List<String> s = sysSynonymService.selectAllSysSynonym();
        //查询最大更新时间
        Date updateDate = sysSynonymService.selectMaxUpdateTime();
        response.setHeader("ETag" ,s.size() + "" );
        response.setHeader("Last-Modified" ,updateDate.getTime() + "" );
        //response.setCharacterEncoding("UTF-8");
        response.setContentType("text/plain;charset=UTF-8");
        String result = "";
        for (String s1 : s) {
            result += s1+"\n";
        }
        return result;
    }
    

    接口返回的数据格式如下
    在这里插入图片描述
    同义词设置

    {\n" +
            "    \"settings\" : {\n" +
            "        \"analysis\" : {\n" +
            "            \"analyzer\" : {\n" +
            "                \"synonym\" : {\n" +
            "                    \"tokenizer\" : \"ik_max_word\",\n" +
            "                    \"filter\" : [\"remote_synonym\"]\n" +
            "               }\n" +
            "            },\n" +
            "            \"filter\" : {\n" +
            "                \"remote_synonym\" : {\n" +
            "                    \"type\" : \"dynamic_synonym\",\n" +
            "                    \"synonyms_path\" : \"http://127.0.0.1:8080/ht/easybiiweb/synonym/synonym\",\n" +
            "                    \"interval\": 60\n" +
            "                }\n" +
            "            }\n" +
            "        }\n" +
            "    }\n" +
            "}
    

    注意:在调用接口时,很有可能会报一下错误
    在这里插入图片描述
    解决方法http://blog.yourtion.com/java-access-denied-socketpermission-solution.html

    展开全文
  • #EXPLAIN语句可以用作DESC(DESCRIBE)的一个同义词,以下效果相同 EXPLAIN book; DESC book; DESCRIBE book;

    #EXPLAIN语句可以用作DESC(DESCRIBE)的一个同义词,以下效果相同
    EXPLAIN book;
    DESC book;
    DESCRIBE book;
    

     

    展开全文
  • 查询数据库同义词sql语句 The concept of SQL Server Synonyms was introduced the first time in SQL Server 2005 as an alias name that references an existing database object, replacing its fully ...

    sql数据库可以创建同义词

    The concept of SQL Server Synonyms was introduced the first time in SQL Server 2005 as an alias name that references an existing database object, replacing its fully qualified name. In this way, it makes the database object more portable and provides more flexibility for the clients to reach and maintain it. You can imagine Synonyms as a layer of abstraction that provides us with an easy way to connect to and manage the database objects without the need to identify the real name and location for these objects.

    SQL Server同义词的概念是在SQL Server 2005中首次引入的,别名是引用现有数据库对象的别名,以代替其完全限定的名称。 这样,它使数据库对象更具可移植性,并为客户端提供了更大的灵活性来访问和维护它。 您可以将同义词想象为一个抽象层,它为我们提供了一种简单的方法来连接和管理数据库对象,而无需标识这些对象的真实名称和位置。

    Synonyms are useful in simplifying complicated and lengthy object names by providing short and friendly alternative names for these database objects. You can benefit from Synonyms by providing backward compatibility for the database objects that are used by legacy systems in case you drop or rename that objects. It may be found to be very simple from the definition, but for database administrators and developers, it would be very useful and simplify their jobs if it is used in a correct way.

    通过为这些数据库对象提供简短友好的替代名称,同义词可以简化复杂而冗长的对象名称。 通过为遗留系统使用的数据库对象提供向后兼容性,您可以从同义词中受益,以防您删除或重命名这些对象。 从定义中可以发现它非常简单,但是对于数据库管理员和开发人员来说,如果使用正确的方式,它将非常有用并简化他们的工作。

    Synonym changes are also transparent from the client application perspective, as no change required from the client side if the Synonym is changed to reference a different database object, as long as the column names are not changed.

    从客户端应用程序的角度看,同义词更改也是透明的,因为只要不更改列名,如果将同义词更改为引用其他数据库对象,则无需从客户端进行更改。

    Assume that you plan to change the name of a database object that is used heavily in your queries. It would seem like a very difficult task, as you need to go through all places in which this object is used. An easy way to perform that is to create a Synonym that references the database object and use that Synonym in your queries. If you need to change the database object name, you need only to change the referenced object only in the Synonym definition, by dropping and recreating the Synonym, without the need to visit all places in which object is mentioned. You can also move the base object easily to another database on the same server or to another SQL server without performing any change from the application side. Just you need to drop the Synonym and recreate a new one that points to the new location of that object.

    假设您打算更改在查询中大量使用的数据库对象的名称。 这似乎是一项非常困难的任务,因为您需要遍历使用此对象的所有地方。 一种简单的执行方法是创建一个引用数据库对象的同义词,然后在查询中使用该同义词。 如果需要更改数据库对象名称,则只需删除并重新创建同义词即可仅更改同义词定义中的引用对象,而无需访问提及该对象的所有位置。 您还可以轻松地将基础对象移动到同一服务器上的另一个数据库或另一个SQL服务器,而无需在应用程序端进行任何更改。 只是您需要删除同义词并重新创建一个指向该对象新位置的新名称。

    Synonyms also help also in obscuring the name of the database objects, for security purposes, by creating a Synonym that references the database object and allows the users to query the Synonym rather than querying the base table directly.

    为了安全起见,同义词还通过创建引用数据库对象并允许用户查询同义词而不是直接查询基表的同义词来帮助掩盖数据库对象的名称。

    The Synonym, like any other database object, should belong to a database schema and should be provided a unique name that follows the T-SQL identifiers rules. The naming convention rules can be also applied to the Synonym, such as using a prefix or suffix with the Synonym name to make it easy to recognize that the database object is a Synonym. A Synonym can be used to reference the following database objects types:

    与其他任何数据库对象一样,同义词应属于数据库架构,并应提供遵循T-SQL标识符规则的唯一名称。 命名约定规则也可以应用到同义词,例如使用带有同义词名称的前缀或后缀,以轻松识别数据库对象是同义词。 同义词可用于引用以下数据库对象类型:

    • Assembly (CLR) stored procedure

      程序集(CLR)存储过程
    • Assembly (CLR) table-valued function

      程序集(CLR)表值函数
    • Assembly (CLR) scalar function

      装配(CLR)标量功能
    • Assembly (CLR) aggregate functions

      程序集(CLR)集合函数
    • Replication-filter-procedure

      复制过滤程序
    • Extended stored procedure

      扩展存储过程
    • SQL scalar function

      SQL标量函数
    • SQL table-valued function

      SQL表值函数
    • SQL inline-tabled-valued function

      SQL内联表值函数
    • SQL stored procedure

      SQL存储过程
    • View

      视图
    • User-defined table

      用户定义表

    On the other hand, you cannot use a Synonym to reference other Synonyms or to reference a user-defined aggregate function. The object that is referenced by a Synonym will be checked at runtime, which means that the Synonym can be created with spelling or referencing errors, but you will get these errors while using that Synonym.

    另一方面,您不能使用同义词来引用其他同义词或引用用户定义的聚合函数。 同义词引用的对象将在运行时检查,这意味着可以使用拼写或引用错误创建同义词,但是使用该同义词时会出现这些错误。

    You can easily drop a Synonym without getting any error or warning messages that it is being referenced by any database object, or you can modify the base object without affecting the Synonym, due to the fact that the Synonyms and the base objects are loosely bonded. Synonyms cannot be referenced in a DDL T-SQL statement, such as modifying using an ALTER statement. Having the fact that the Synonyms are not schema-bound database objects, it cannot be referenced by schema-bound expressions such as:

    您可以轻松删除同义词,而不会收到任何数据库对象正在引用的任何错误或警告消息,或者您可以修改基础对象而不影响同义词,因为同义词和基础对象是松散结合的。 不能在DDL T-SQL语句中引用同义词,例如使用ALTER语句进行修改。 由于同义词不是架构绑定的数据库对象,因此不能由架构绑定的表达式引用它,例如:

    • CHECK constraints

      检查约束
    • Computed columns

      计算列
    • Default expressions

      默认表达式
    • Rule expressions

      规则表达式
    • Schema-bound views

      架构绑定视图
    • Schema-bound functions

      架构绑定功能

    To create a Synonym that references objects across schemas, databases and servers, you need to specify the schema and the name of the Synonym and the schema and the name of the database object that the synonym references. The syntax that is used to create a new Synonym is as shown below:

    要创建引用跨架构,数据库和服务器的对象的同义词,您需要指定架构和同义词的名称以及架构和同义词引用的数据库对象的名称。 创建新同义词的语法如下所示:

    CREATE SYNONYM schema_name_1. synonym_name FOR server_name. database_name. schema_name_2. object_name

    创建SYNONYM schema_name_1。 synonym_name FOR服务器名。 数据库名称。 schema_name_2。 object_name

    For a new Synonym, you need to provide the schema_name_1 that specifies the schema in which the synonym will be created, with the default schema of the current user will be used if the schema is not specified in the CREATE SYNONYM statement and the synonym_name that specifies the name of the new synonym.

    对于新的同义词,您需要提供用于指定将在其中创建同义词的模式的schema_name_1,如果未在CREATE SYNONYM语句中指定该模式,并且指定了synonym_name,则将使用当前用户的默认模式新同义词的名称。

    For the referenced object, you can provide the server_name that specifies the name of the server on which base object is located, the database_name that is the name of the database in which the base object is located, the schema_name_2 that is the name of the schema of the base object, with the default schema of the current user will be used if it is not provided, and the object_name that specifies the name of the base object that will be referenced by the Synonym.

    对于引用的对象,您可以提供server_name,该server_name指定基础对象所在的服务器的名称,database_name是基础对象所在的数据库的名称,schema_name_2是该架构的名称。基础对象的名称,如果未提供,则使用当前用户的默认架构,而object_name指定将由同义词引用的基础对象的名称。

    To be able to create a Synonym in the provided schema, you should have CREATE SYNONYM permissions with db_owner permissions in that schema or, at least at a minimum, ALTER SCHEMA permissions. Synonyms can be also created with the New Synonym window using SQL Server Management Studio, by right-clicking on the Synonyms node under the current database as shown below:

    为了能够在提供的架构中创建同义词,您应该具有在该架构中具有db_owner权限的CREATE SYNONYM权限,或者至少具有ALTER SCHEMA权限。 也可以使用SQL Server Management Studio在“新建同义词”窗口中创建同义词,方法是右键单击当前数据库下的“同义词”节点,如下所示:

    Where you can provide the previously described parameters, such as the Synonym schema and name and the server name, database name, schema name and the name of the referenced object, in order to create the Synonym as follows:

    您可以在其中提供先前描述的参数,例如“同义词”模式和名称以及服务器名称,数据库名称,模式名称和被引用对象的名称,以便按以下方式创建同义词:

    Once created, you can perform SELECT, INSERT, UPDATE, DELETE or EXECUTE operations on that Synonym. To be able to perform these operations, a number of permissions should be granted on the Synonym, such as:

    创建完成后,您可以对该同义词执行SELECT,INSERT,UPDATE,DELETE或EXECUTE操作。 为了能够执行这些操作,应在同义词上授予许多权限,例如:

    • CONTROL

      控制
    • DELETE

      删除
    • EXECUTE

      执行
    • INSERT

    • SELECT

      选择
    • TAKE OWNERSHIP

      取得所有权
    • UPDATE

      更新
    • VIEW DEFINITION

      查看定义

    Synonym owners or users with db_owner or db_ddladmin permissions can GRANT, DENY or REVOKE permission at the Synonym level, with no effect at the base table level. The below script is used to create a new database user, and grant it SELECT permission at the Synonym level:

    同义词所有者或具有db_owner或db_ddladmin权限的用户可以在同义词级别上授予GRANT,DENY或REVOKE权限,而在基表级别上则无效。 以下脚本用于创建新的数据库用户,并在“同义词”级别授予其SELECT权限:

     
    USE [SQLShackDemo]
    GO
    CREATE USER [suheir] FOR LOGIN [suheir]
    GO
    GRANT SELECT ON [dbo].[MySynonym] TO [suheir]
     
    

    创建一个同义词来引用本地对象 (Creating a synonym to reference a local object)

    The below T-SQL statement is used to create a Synonym to reference a local table in the same SQL Server instance, where the base database name, the schema and the table name are provided in the CREATE SYNONYM statement:

    下面的T-SQL语句用于创建同义词以引用同一SQL Server实例中的本地表,其中在CREATE SYNONYM语句中提供了基础数据库名称,架构和表名称:

     
    USE SQLShackDemo
    GO
    CREATE SYNONYM dbo.MySynonym
    FOR SQLShackDemo.dbo.SynTestNew;
    GO
     
    

    After creating the Synonym, you can easily retrieve data from it directly using the SELECT statement shown below:

    创建同义词后,您可以使用下面所示的SELECT语句轻松地直接从中检索数据:

     
      SELECT TOP 10 * FROM MySynonym
     
    

    The SELECT statement result will retrieve the data from the base table directly as follows:

    SELECT语句的结果将直接从基表中检索数据,如下所示:

    创建同义词来引用远程对象 (Creating a synonym to reference a remote object)

    As mentioned previously within this article, Synonyms can be used to simplify the name of the base object by using a short alias instead of using the full object name. The below T-SQL statement is used to create a Synonym to reference a database table hosted in a remote SQL Server instance, where the server name, the base database name, the schema and the table name are provided in the CREATE SYNONYM statement:

    如本文前面所述,可以通过使用短别名而不是完整的对象名称来使用同义词来简化基础对象的名称。 下面的T-SQL语句用于创建同义词,以引用远程SQL Server实例中托管的数据库表,其中CREATE SYNONYM语句中提供了服务器名称,基本数据库名称,架构和表名称:

     
    USE SQLShackDemo
    GO
    CREATE SYNONYM DevSyn
    FOR DEV.Testdb.dbo.InvoiceTest;
    GO
     
    

    Once created, you can easily retrieve the data by querying the Synonym simple name directly as in the SELECT statement shown below:

    创建完成后,您可以直接查询“同义词”简单名称来轻松检索数据,如下面的SELECT语句所示:

     
    SELECT TOP 10 * FROM DevSyn
     
    

    Instead of using the full four-part name:

    而不使用完整的四部分名称:

     
    SELECT TOP 10 * FROM DEV.Testdb.dbo.InvoiceTest;
     
    

    Both queries will return the same result as shown below:

    这两个查询将返回相同的结果,如下所示:

    If you check the execution plan of the previous SELECT statement using ApexSQL Plan, the execution plan viewer and query optimization tool, you will see that the Synonym name will be translated to the base table name and location when the query is executed as clear from the execution plan below:

    如果使用ApexSQL Plan( 执行计划查看器和查询优化工具)检查上一个SELECT语句的执行计划 ,您将看到执行查询时 ,同义词名称将转换为基表名称和位置,从执行计划如下:

    The screenshot is from ApexSQL Plan, a free tool to view and analyze SQL Server query execution plans

    屏幕截图来自ApexSQL PlanApexSQL Plan是查看和分析SQL Server查询执行计划的免费工具

    创建同义词以引用用户定义的函数 (Creating a synonym to reference a user-defined function)

    A Synonym can be also used to reference user-defined functions. That allows you to rename or move that function to another SQL Server instance without affecting the clients, by dropping and recreating the Synonym one time to reference the new user-function name or location.

    同义词也可以用来引用用户定义的函数。 这样,您可以一次删除并重新创建“同义词”以引用新的用户函数名称或位置,从而在不影响客户端的情况下将函数重命名或移动到另一个SQL Server实例。

    Let’s create a simple user-defined function that will return the absolute value of the negative integers:

    让我们创建一个简单的用户定义函数,该函数将返回负整数的绝对值:

     
    USE SQLShackDemo
    GO
    CREATE FUNCTION dbo.TestSynFunction (@InputNum int)  
    RETURNS int  
    AS  
    BEGIN  
    IF @InputNum < 0  
    BEGIN  
        SET @InputNum= abs(@InputNum)
    END  
    RETURN(@InputNum);  
    END;  
    GO
     
    

    Then we will create a Synonym that references the created function:

    然后,我们将创建一个引用创建函数的同义词:

     
    CREATE SYNONYM dbo.AbsVal  
    FOR dbo.TestSynFunction;  
    GO
     
    

    We can easily call the function within our code by selecting from it, passing the required parameters, as shown below:

    我们可以通过在代码中进行选择,并传递所需的参数来轻松调用该函数,如下所示:

     
    DECLARE @Num int;  
    SET @Num = -7;  
    SELECT dbo.AbsVal(@Num) AS ABSValue;  
     
    

    The absolute value of the passed negative integer will be like:

    传递的负整数的绝对值将类似于:

    SQL Server allows you to perform different types of operations on the Synonyms, that change the data and not the schema of the object, such as SELECT, INSERT, UPDATE, DELETE or EXECUTE operations. When you perform a data modification operation, let’s say an UPDATE statement, the base object will be affected. For example, if you update a row in a Synonym that references a database base table, you will actually update that row in the referenced table too.

    SQL Server允许您对同义词执行不同类型的操作,这些操作会更改数据而不是对象的架构,例如SELECT,INSERT,UPDATE,DELETE或EXECUTE操作。 当您执行数据修改操作时,假设一条UPDATE语句,基础对象将受到影响。 例如,如果您更新引用数据库基表的同义词中的一行,则实际上您也将同时更新所引用表中的该行。

    Assume that we need to update the EmpPhone of the below employee:

    假设我们需要更新以下员工的EmpPhone:

    The following UPDATE statement is used to update that employee information from the Synonym itself:

    以下UPDATE语句用于从同义词本身更新该员工信息:

     
    USE [SQLShackDemo]
    GO
    UPDATE [dbo].[MySynonym] SET EmpPhone='612-854-3528' WHERE ID = 2010001
     
    

    Checking the execution plan of the previous UPDATE statement that is generated, the plan will show us that the query performed a clustered index update on the base table, SynTestNew table, as shown below:

    检查生成的上一条UPDATE语句的执行计划,该计划将向我们显示该查询对基表SynTestNew表执行了聚集索引更新,如下所示:

    The screenshot is from ApexSQL Plan, a free tool to view and analyze SQL Server query execution plans

    屏幕截图来自ApexSQL PlanApexSQL Plan是查看和分析SQL Server查询执行计划的免费工具

    Which is also clear when we retrieve that employee’s information from the base table directly as shown below:

    当我们直接从基表中检索该员工的信息时,这也很明显,如下所示:

    The sys.synonyms DMV can be queried for the synonyms information in a specific database. Useful synonym metadata information retrieved from that DMV such as the synonym name and the name of the base object. The T-SQL statement below is used to query the sys.synonyms DMV for the metadata information of the Synonyms created under the SQLShackDemo testing database:

    可以在sys.synonyms DMV中查询特定数据库中的同义词信息。 从该DMV检索到的有用的同义词元数据信息,例如同义词名称和基础对象的名称。 下面的T-SQL语句用于查询sys.synonyms DMV以获取在SQLShackDemo测试数据库下创建的同义词的元数据信息:

     
    USE SQLShackDemo
    GO
    SELECT name, type_desc, create_date,base_object_name FROM sys.synonyms
     
    

    The list of created Synonyms under SQLShackDemo database are shown below:

    在SQLShackDemo数据库下创建的同义词列表如下所示:

    The base type of the base object referenced by a Synonym can be checked using the OBJECTPROPERTYEX function. The below T-SQL statement retrieve the type of the base object for the previously created Synonyms:

    可以使用OBJECTPROPERTYEX函数检查由同义词引用的基础对象的基础类型。 下面的T-SQL语句检索先前创建的同义词的基础对象的类型:

     
    USE SQLShackDemo
    GO
    SELECT OBJECTPROPERTYEX(OBJECT_ID('MySynonym'), 'BaseType') AS LocalSyn_BaseType;  
    GO
    SELECT OBJECTPROPERTYEX(OBJECT_ID('DevSyn'), 'BaseType') AS RemoteSyn_BaseType;  
    GO
    SELECT OBJECTPROPERTYEX(OBJECT_ID('AbsVal'), 'BaseType') AS FunctionSyn_BaseType;  
    GO
     
    

    The result shows that the type of the local and remote base objects that are referenced by the first two Synonyms is User Table, and the type of the base object referenced by the last Synonym is User-Defined Function as below:

    结果显示,前两个同义词所引用的本地和远程基础对象的类型为“用户表”,最后一个同义词所引用的基础对象的类型为“用户定义的函数”,如下所示:

    You can also browse all Synonyms created under a specific database from the Synonyms node under the current database using SQL Server Management Studio as follows:

    您还可以使用SQL Server Management Studio从当前数据库下的“同义词”节点浏览在特定数据库下创建的所有同义词,如下所示:

    Where you can check the properties of each Synonym from the Synonym Properties window, including the referenced base object server, database, schema and name as clear from the image below:

    在“同义词属性”窗口中,您可以在其中检查每个同义词的属性,包括从下图清晰可见的引用的基础对象服务器,数据库,架构和名称:

    A Synonym can be dropped at any time, without affecting the base object. Dropping the Synonym is required when you need to rename or move the Synonym referenced object in order to create a new Synonym with the new change, as you cannot use the ALTER statement to modify that Synonym. The Synonym can be simply dropped by passing the Synonym name to the DROP SYNONYM T-SQL statement as in the script below:

    可以随时删除同义词,而不会影响基础对象。 当您需要重命名或移动同义词引用的对象以使用新更改创建新的同义词时,需要删除同义词,因为您不能使用ALTER语句来修改该同义词。 可以通过将同义词名称传递给DROP SYNONYM T-SQL语句来简单地删除同义词,如以下脚本所示:

     
    USE [SQLShackDemo]
    GO
    DROP SYNONYM [dbo].[AbsVal]
    GO
     
    

    结论 (Conclusion)

    SQL Server Synonyms are very useful in simplifying and shortening the database objects names, in a way it makes these objects usage easier and more flexible.

    SQL Server同义词在简化和缩短数据库对象名称方面非常有用,它可以使这些对象的使用更轻松,更灵活。

    Synonyms also provide us with a layer of abstraction that helps us to use and manage these database objects without the need to identify the real name and location of these objects.

    同义词还为我们提供了一层抽象,可以帮助我们使用和管理这些数据库对象,而无需标识这些对象的真实名称和位置。

    Within this article, we described how to use the Synonym to reference local and remote database objects, in addition, to reference a user-defined function. Enjoy using the Synonyms in your environment, in the correct way, with the best methods to get metadata about these Synonyms as mentioned within this article.

    在本文中,我们描述了如何使用同义词来引用本地和远程数据库对象,以及引用用户定义的函数。 享受以正确的方式在您的环境中使用同义词的方法,以及获得本文中提到的有关这些同义词的元数据的最佳方法。

    翻译自: https://www.sqlshack.com/simplify-sql-server-database-object-usage-synonyms/

    sql数据库可以创建同义词

    展开全文
  • mysql同义词的一个模拟

    千次阅读 2013-11-25 21:59:19
    mysql没提供同义词的功能,oracle有,但其实可以简单模拟下。比如: CREATE DATABASE seussdb; CREATE DATABASE appdb; GRANT ALL ON seussdb.* TO student; GRANT ALL ON appdb.* TO student; 有两...
  • Oracle 数据库同义词

    千次阅读 2019-02-26 15:01:13
    一、Oracle数据只有一个实例(简单理解就是Oracle 只能建立一个数据库,不像MySQL,它下面可以创建N个库),那么Oracle是根据用户灵活去管理的;这点读起来、理解   起来也不那么难,但是除非自己亲自实现一把才...
  • Mysql数据库复习大纲

    2020-07-14 09:29:30
    1 MySQL数据库概要 2 简单MySQL环境 3 数据的存储和获取 4 MySQL基本操作 5 函数 6 数据查询 核心技术 7 索引 8 存储过程 9 触发器 10 视图 11 事务 12 系统管理 高级技术 13 数据库备份与还原 14 MySQL性能优化 15 ...
  • MySQL数据库

    2021-01-05 22:16:12
    typora-root-url: MySQL数据库.assets MySQL数据库 一、为什么要使用数据库 持久化(persistence):把数据保存到可掉电式存储设备中以供之后使用。大多数情况下,特别是企业级应用,数据持久化意味着将内存中的数据...
  • 同义词是指数据库对象(表、视图、序列、存储过程等数据库对象)用另一个名字来引用。比如: 建立一个同义词可以排除一个对象名字的限制.  如果你的数据库有多个用户,USER_A要访问USER_B的TABLE1,只能使用USER_B....
  • mysql数据库

    千次阅读 2020-05-15 14:32:43
    目录1、数据库介绍2、mysql安装3、mysql管理4、mysql数据类型5、常用mysql命令创建数据表插入数据查询数据where 子句LIKE 子句UPDATE 查询DELETE 语句排序GROUP BY 语句ALTER命令外键NULL值处理连接事务索引 ...
  • Mysql数据库

    千次阅读 2019-01-10 17:58:14
    关系型数据库基础 安装MySQL 管理数据库和表 函数和存储过程 用户和权限管理 MySQL架构 存储引擎 服务器选项,系统和状态变量 优化查询和索引管理 锁和事务管理 日志管理 备份还原 MySQL集群 1、关系型...
  • 在做需求的时候发现,正常来讲的话查询的是数据库的表,但是有时查询的可能又是视图,还有可能查询的是一个表的同义词;表/视图/同义词 他们有什么关联,为什么会有这么三种定义呢?数据库基表数据库中的表,统一定义为基表...
  • MySQL数据库命令

    2020-02-09 20:22:44
    MySQL数据库 1.1什么是MySQL数据库 MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据...
  • MySQL数据库基础

    2021-03-26 09:38:24
    MySQL数据库 数据库的基本概念 1、数据库的英文单词: DataBase 简称:DB 2、什么是数据库? 用于存储数据和管理数据的仓库 3、数据库的特点: 1、持久化存储数据。其实数据库就是一个文件系统 2、方便存储和管理...
  • MySQL数据库 Oracle数据库 初始密码: 数据库对象 库/模式 数据表 视图 触发器 函数与存储过程 序列与同义词 SQL语言
  • Mysql数据库基础

    2020-07-13 13:40:28
    mysql数据库 什么是mysql? MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一 MySQL 是开源的,所以你不...
  • MySQL数据库衍生版,也被视为开源数据库MySQL的替代品。...虽然他们确实存在很多差异,但是在这里,我们将使用 MariaDB 和 MySQL 作为同义词。也就是说,如果您看到我说 MariaDB 不用感到困惑,那就是 MySQ...
  • 一、Oracle数据只有一个实例(简单理解就是Oracle 只能建立一个数据库,不像MySQL,它下面可以创建N个库),那么Oracle是根据用户灵活去管理的;这点读起来、理解 起来也不那么难,但是除非自己亲自实现一把才理解...
  • mysql数据库

    2020-02-11 14:17:33
    数据库 DataBase,DB 持久化储存数据,是一个文件管理系统 方便储存和管理数据 使用类统一的方法操作数据库-----SQl MySQL卸载 找到MySQl的安装目录 找到my.ini文件打开 复制出#Path to the database root,默认...
  • MySQL数据库的使用

    2019-04-02 14:51:33
    1.MySQL简介 a)MySQL是一个开源免费的关系型数据库管理系统。 b)默认用户:root c)默认端口号:3306 ...Integer,int的同义词 bigint,8字节 2.浮点数 float,4字节 double,8字节 2.1.2 字符类型 char,...
  • MySQL数据库学习·数据库的创建,修改,删除。
  • 1、MySQL 创建数据库 我们可以在登陆 MySQL 服务后,使用 create 命令创建数据库,语法如下: CREATE DATABASE 数据库名;
  • mysql 数据库操作

    2020-02-19 16:08:08
    创建数据库:CREATE DATABASE 数据库名; 更复杂: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | ...
  • MySQL数据库操作

    2019-04-08 18:53:17
    MySQL创建数据库 我们在登录MySQL后,创建数据库名字,用create来创建; 举例说明: create database 数据库名字; MySQL - u- root -p enter password:******#输入密码登录 create database 数据库名字; ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 9,029
精华内容 3,611
关键字:

mysql数据库同义词

mysql 订阅